CODEWITHSUNDEEP
mysqljoin
Jim
Jim

Reputation: 923

MySQL - JOIN with relation table

I have three tables 

USER
---------------------------------------
id | uid | first_name | last_name | ...
---------------------------------------
0  | 95  | ...        | ...       | ...   
1  | 100 | ...        | ...       | ...     
2  | 120 | ...        | ...       | ...
3  | 130 | ...        | ...       | ...

NEWS_mm
------------------------
uid_local | uid_foreign 
------------------------
40        | 90        
40        | 100 
50        | 120 
50        | 130

NEWS
-------------------------------
id | uid  | title | image | ...
-------------------------------
0  | 40   | ...   | ...   | ...    
1  | 50   | ...   | ...   | ...
2  | 60   | ...   | ...   | ...

Now i want only select all users from table "USER" which have the news id 50 from table "NEWS". NEWS_mm has the news id (id_local) from "NEWS" and the user id (id_foreign) from "USER"

SELECT USER.*, NEWS_MM.*, NEWS.*
FROM USER
    JOIN NEWS_MM
        ON NEWS_MM.uid_foreign = USER.uid
    JOIN NEWS
        ON NEWS_MM.uid_local = NEWS_MM.uid_local
WHERE NEWS.uid = 50

Upvotes: 0

Views: 83

Answers (1)

rs'
rs'

Reputation: 311

you are on the right lines but you are joining the table NEWS on NEWS_MM = NEWS_MM I suspect you want to join like so:

SELECT USER.*, NEWS_MM.*, NEWS.*
FROM USER
JOIN NEWS_MM
    ON NEWS_MM.uid_foreign = USER.uid
JOIN NEWS
    ON NEWS_MM.uid_local = NEWS.uid
WHERE NEWS.uid = 50

also note that you know that the value of NEWS_MM.uid_local is the same as NEWS.uid so you don't actually need the second join at all if you only want the data from the USER table which should give better performance

SELECT 
  NEWS_MM.uid_local, 
  USER.* 
FROM USER
  JOIN NEWS_MM
    ON NEWS_MM.uid_foreign = USER.uid
WHERE NEWS_MM.uid_local = 50

Upvotes: 2

Related Questions