0

I'm trying to achieve MySQL's version of a FULL JOIN with no luck. My results need to include ALL rows from 'users' whether or not the user has matching rows in the other tables. Right now my results are not all due to nature of LEFT JOIN but when I UNION ALL, LEFT and RIGHT, my results are narrowed even further. 

SELECT 
    users.*, profile_status.*, photos.*, general.*, experience.*, occupation.*, 
    flagged.*, last_update.*, job_search.*, users.user_id AS userID, 
    user_profile_status.user_id AS profileID
FROM users
    JOIN profile_status ON users.user_id = profile_status.user_id
    LEFT JOIN photos ON users.user_id = photos.user_id AND photos.selected = 1
    LEFT JOIN general ON users.user_id = general.user_id
    LEFT JOIN occupation ON users.user_id = occupation.user_id
    LEFT JOIN experience ON users.user_id = experience.user_id
    LEFT JOIN flagged ON users.user_id = flagged.user_id
    LEFT JOIN job_search ON users.user_id = job_search.user_id
    LEFT JOIN last_update ON users.user_id = last_update.user_id
WHERE 
    users.approved = 1 AND users.admin_level = 0
ORDER BY last_update.last_update DESC

My attempt on UNION

SELECT 
    users.*, profile_status.*, photos.*, general.*, experience.*, occupation.*, 
    flagged.*, last_update.*, job_search.*, users.user_id AS userID, 
    user_profile_status.user_id AS profileID
FROM users
    JOIN profile_status ON users.user_id = profile_status.user_id
    LEFT JOIN photos ON users.user_id = photos.user_id AND photos.selected = 1
    LEFT JOIN general ON users.user_id = general.user_id
    LEFT JOIN occupation ON users.user_id = occupation.user_id
    LEFT JOIN experience ON users.user_id = experience.user_id
    LEFT JOIN flagged ON users.user_id = flagged.user_id
    LEFT JOIN job_search ON users.user_id = job_search.user_id
    LEFT JOIN last_update ON users.user_id = last_update.user_id
UNION ALL
    SELECT 
    users.*, profile_status.*, photos.*, general.*, experience.*, occupation.*, 
    flagged.*, last_update.*, job_search.*, users.user_id AS userID, 
    user_profile_status.user_id AS profileID
FROM users
    JOIN profile_status ON users.user_id = profile_status.user_id
    RIGHT JOIN photos ON users.user_id = photos.user_id AND photos.selected = 1
    RIGHT JOIN general ON users.user_id = general.user_id
    RIGHT JOIN occupation ON users.user_id = occupation.user_id
    RIGHT JOIN experience ON users.user_id = experience.user_id
    RIGHT JOIN flagged ON users.user_id = flagged.user_id
    RIGHT JOIN job_search ON users.user_id = job_search.user_id
    RIGHT JOIN last_update ON users.user_id = last_update.user_id
WHERE 
    users.approved = 1 AND users.admin_level = 0
ORDER BY last_update.last_update DESC

Table structure:

Table users
user_id  first_name   last_name   admin_level   user_approved  
1        tommy        jones       0             1      
2        johnny       rocket      0             1      
3        sally        hotpants    0             1  

Table profile_status
user_id  photo_uploaded   
1        1
2        1
3        1

Table photos
photo_id  user_id  filename      selected
1         1        photo_1.jpg   1
2         2        photo_2.jpg   0
3         2        photo_2.jpg   1
4         3        photo_3.jpg   1

Table general
user_id  city        state      zip     neighborhood
1        baltimore   maryland   00125   hamsterdam
2        lakeland    maine      11542   treemont
2        tipper      vermont    08483   NULL

Table experience
user_id  waldorf  kumon  homeschooling 
1        0       1       0
2        0       0       1

Table occupation
user_id  occupation 
1        teacher  
2        student    
3        student 

Table job_search
search_id  user_id  job
1          1        art teacher  
2          2        designer 

Table flagged
user_id  flagged 
1        1  
2        0    

Table last_update
update_id  user_id  last_update           photo_update
1          1        0000-00-00 00:00:00   0000-00-00 00:00:00
2          2        0000-00-00 00:00:00   0000-00-00 00:00:00
Klav
  • 405
  • 1
  • 9
  • 19
  • The second part of the UNION ALL makes little sense. About the first part: any chance that there could be users without a matching record on ```profile_status```? – ffflabs Oct 13 '14 at 23:04
  • From seeing other examples it looked like I could LEFT JOIN then UNION then RIGHT JOIN and get similar results to FULL JOIN. Users and profile_status are created simultaneously so there is no chance there is a user without a corresponding profile_status record – Klav Oct 13 '14 at 23:07
  • 1
    Just change the first `JOIN` to `LEFT JOIN` and the query should do what you want. – Gordon Linoff Oct 13 '14 at 23:09
  • I get it, you're trying to mimic a FULL OUTER JOIN, it just makes no sense here, since you are doing a RIGHT JOIN but including the user table in the WHERE clause. Moreover, if you want to get all the matching rows from user table, why would you need to get the rows which doesn't exist in that very table? – ffflabs Oct 13 '14 at 23:10
  • @GordonLinoff thanks I'll give that a try. That would do the trick even if there are already full matching records between users & profile_status? – Klav Oct 13 '14 at 23:11
  • @amenadiel The output can contain NULL for specific pieces, such as a users neighborhood, occupation, or even last_update – Klav Oct 13 '14 at 23:12
  • But a NULL user row won't ever have ```users.approved = 1 AND users.admin_level = 0``` – ffflabs Oct 13 '14 at 23:13
  • In this case users will never be null, it's the other tables narrowing down the results – Klav Oct 13 '14 at 23:34
  • I think the `JOIN` to `LEFT JOIN` on `profile_status` did the trick. Confirming – Klav Oct 13 '14 at 23:43
  • 1
    @Jamie . . . A chain of `left outer join`s will keep all the rows in the first table, regardless of whether there are matches in the other tables. The `where` clause is only on the first table, so it filters those rows -- which is presumably what you want the query to do. – Gordon Linoff Oct 14 '14 at 09:08
  • @GordonLinoff Thanks a bunch, this is working nicely. I've changed all LEFT JOINS to LEFT OUTER JOIN, the only place I'm running into issues is `LEFT OUTER JOIN photos ON users.user_id = photos.user_id AND photos.selected = 1` If I remove `photos.selected = 1` I get extra results for users with multiple photos. But if I leave it my results are narrowed – Klav Oct 14 '14 at 13:47

0 Answers0