CODEWITHSUNDEEP
mysqlsqljoin
Jim
Jim

Reputation: 923

SQL - "merge" 3 tables with JOIN

I want to select users from a table depending from two relation tables

The Structure:

[user]

uid | firstname | lastname | ...
---------------------------------
482 | USERa     | USERa    | ...
885 | USERb     | USERb    | ...
405 | USERc     | USERc    | ...
385 | USERd     | USERd    | ...

[news_info]

uid_local   | uid_foreign
--------------------------------
125         | 482       
100         | 405       

[news_add]

uid_local   | uid_foreign
--------------------------------
125         | 885   
105         | 385

Now i want to select only USERa and USERb via uid_local -> 125, from [news_info] and [news_add]

SELECT 
  nnfo.uid_local, 
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125

result = USERa // works

SELECT 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125

result = USERb // works

now "merge" the sql statement into one...to get USERa and USERb

SELECT 
  nnfo.uid_local, 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 AND nadd.uid_local = 125

result = empty // no errors.....

what is wrong...i tryed some other statements but with no result :(

Upvotes: 2

Views: 107

Answers (2)

davek
davek

Reputation: 22925

Your uid_local IDs exist on different rows: so when you want to AND the results, you get nothing (as nothing intersects). You could do it with a union:

SELECT * from
(
SELECT 
  nnfo.uid_local, 
  user.firstname, user.lastname 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 

UNION ALL

SELECT 
  nadd.uid_local,
  user.firstname, user.lastname 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125
) x

Upvotes: 2

Sebas
Sebas

Reputation: 21542

When merging results of queries, there are 2 different approaches:

  • UNION
  • JOINs

To know which one to choose you have to know whether there's a technical link between tables in the 2 queries you wish to merge. In your case, there's a functional link (nnfo.uid_local = 125 AND nadd.uid_local = 125) but it's not enough to join these 2 universes. You still could get the result with only one SELECT, but I think the final result would be unclear and somehow not really in the spirit of what SQL provides.

I'd go towards the UNION solution:

SELECT 
  nnfo.uid_local, 
  user.* 
FROM user
  JOIN news_info nnfo
    ON nnfo.uid_foreign = user.uid
WHERE nnfo.uid_local = 125 
UNION
SELECT 
  nadd.uid_local,
  user.* 
FROM user
  JOIN news_add nadd
    ON nadd.uid_foreign = user.uid
WHERE nadd.uid_local = 125

Edit: I see davek's result implements UNION ALL instead of UNION. I assumed you wished to return USERa only once if it happened to appear in both independant queries, that's why I used the simple UNION operator vs the UNION ALL that would return duplicate results as well.

Upvotes: 1

Related Questions