Two joins in mysql query not returning expected result

Question

I am working on a join and I cannot seem to get the resultset that I need. Let me paint the scenario:

I have 2 tables:

Data table

+----+-------+
| ID | Name  |
+----+-------+
| 10 | Test1 |
| 11 | Test2 |
| 12 | Test3 |
| 13 | Test4 |
| 14 | Test5 |
| 15 | Test6 |
+----+-------+

Join table

+----+-----+-----+-----+
| ID | FID | GID | Val |
+----+-----+-----+-----+
| 10 |   3 |     | abc |
| 10 |     |   1 | def |
| 11 |   3 |     | ijk |
| 12 |     |   1 | lmn |
| 13 |   4 |     | opq |
+----+-----+-----+-----+

Expected Result Set

+---------------+-----------------+---------------+----------------+----------------+
| Data table id | Data table name | Join Tabe FID | Join Table GID | Join Table Val |
+---------------+-----------------+---------------+----------------+----------------+
|            10 | Test1           |             3 |                | abc            |
|            11 | test2           |             3 |                | ijk            |
|            12 | test3           |               |              1 | lmn            |
+---------------+-----------------+---------------+----------------+----------------+

My Query

Select
   *
from
   datatable A
join jointable b
on
   A.ID = B.ID
   and B.FID = 3
join jointable c
on
   A.ID = C.ID
   and C.GID = 1
   and C.FID <> null

What is happening is that the join on table C is being done on the resultset of the join between table A and B, therefore the resultset is blank.

I want the join on table C to be applied on table A and not on the resultset from the join between table A and B; which will result on the expected resultset.

Can anyone help?

Thanks

Answer 1

The expression C.FID <> null will never evaluate to true, it will always return NULL. An inequality comparison to NULL will always evaluate to NULL. (In SQL, in a boolean context, en expression will evaluate to one of three possible values: TRUE, FALSE or NULL.)

If you want a comparison to NULL to return TRUE or FALSE, use an IS [NOT] NULL comparison test. An expression like

foo IS NULL

or

foo IS NOT NULL

Or, you could make use the MySQL specific null-safe comparison (spaceship) operator:

foo <=> NULL

or

NOT (foo <=> NULL)

---

As to the result you want to return, it's a bit confusing as to how you arrive at what you want to return.

To me, it looks like you are wanting to get the matching rows from jointable... if there are matching rows with fid=3, return just those rows. If there aren't any matching rows with fid=3, then return rows that have a NULL value in fid and gid=1.

If that's what we want returned, we can write a query that does that. If that's not what we want returned, then the rest of this answer doesn't matter.

We can use a NOT EXISTS predicate to test for the non existence of matching rows.

For example:

SELECT d.id 
     , d.name
     , j.fid
     , j.gid
     , j.val
  FROM datatable d
  JOIN jointable j
    ON j.id = d.id
 WHERE ( j.fid = 3 )
    OR ( j.fid IS NULL
         AND j.gid = 1 
         AND NOT EXISTS ( SELECT 1
                            FROM jointable t
                           WHERE t.id = d.id
                             AND t.fid = 3
                        )
       )