SQL join exclude results

Question

This is probably something really easy but I cant figure it out at the moment.

Table Order

+----+---------+
| id |  name   |
+----+---------+
| 1  | Order 1 |
| 2  | Order 2 |
| 3  | Order 3 |
+----+---------+

Table Facturationdetails

+----+----------------+
| id |      name      |
+----+----------------+
| 1  | Transportation |
| 2  | Regular        |
| 3  | Fixed          |
+----+----------------+

Table Relation:

Table Facturationdetails

+----------+---------+
| order_id | fact_id |
+----------+---------+
| 1        | 1       |
| 1        | 2       |
| 1        | 3       |
| 2        | 2       |
| 2        | 3       |
| 3        | 2       |
+----------+---------+

Now I would like to find out for which order there are no fakturationdetails1(Transportation)

select to.order_id
from table_order to
join table_facturation tf
on tf.order_id = to.order_id
where tf.fakt_id != 1

But this will return all rows:

+---+---+
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 3 | 2 |
+---+---+

And I want the results to be:

Order 2 and Order 3.

Answer 1

I suspect you want to use NOT EXISTS, so rather than finding rows where the fact_id isn't 1, find orders from the table where the fact_id is 1, then exclude those orders:

SELECT  o.order_id
FROM    table_order o
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    table_facturation tf
            WHERE   tf.order_id = o.order_id
            AND     tf.fakt_id = 1
        );

You have not specified a DBMS, but if you are using MySQL you will get better performance using LEFT JOIN\IS NULL:

SELECT  o.order_id
FROM    table_order o
        LEFT JOIN table_facturation tf
            ON tf.order_id = o.order_id
            AND tf.fakt_id = 1
WHERE   tf.order_id IS NULL;

Answer 2

One way to approach this is with a left join and comparison in the where clause. Look for things that match, and then choose those with no match:

select to.order_id
from table_order to left join
     table_facturation tf
     on tf.order_id = to.order_id and
        tf.fakt_id = 1
where tf.fakt_id is null;