CODEWITHSUNDEEP
mysqlsqljoinresultset
Yash
Yash

Reputation: 2289

Two joins in mysql query not returning expected result

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

Upvotes: 0

Views: 117

Answers (5)

spencer7593
spencer7593

Reputation: 108390

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
                        )
       )

Upvotes: 1

White Feather
White Feather

Reputation: 2783

SELECT
*
FROM datatable A
LEFT JOIN jointable B ON A.ID = B.ID 
WHERE B.FID = 3 OR B.GID = 1;

This will return you:

10  Test1   10  3       abc
10  Test1   10      1   def
11  Test2   11  3       ijk
12  Test3   12      1   lmn

Now, it seems you want to filter out:

10  Test1   10  3       abc

and keep 

10  Test1   10      1   def

Is that what you want?

Regards

Upvotes: 1

mh-dev
mh-dev

Reputation: 5503

To be honest not sure if I understand what you want, so this might not be correct. What I think you want is. You have two tables and want to join all rows from the join table where the id matches and fid= 3 or gid=1 and fid not null. The query for this should be something like this. (one join should be enough)

Select
   *
from
   datatable A
join jointable b
on
   A.ID = B.ID
where b.fid = 3 or (b.gid = 1 and b.fid <> null)

Upvotes: 0

Justinas Jakavonis
Justinas Jakavonis

Reputation: 8798

SELECT * FROM db1 
INNER JOIN db2 ON db1.id = db2.id
WHERE db1.FID = 3 or db2.GID = 1;

Why do you use C.FID <> null when B.GID = 1? It should be null.

Upvotes: 0

L Kefalas
L Kefalas

Reputation: 63

SELECT *
FROM   table1 a
INNER JOIN table2 b ON a.ID = b.ID
WHERE b.FID = 3 OR b.GID = 1

But your question is not very specific as far as the WHERE clauses

Upvotes: 0

Related Questions