I have 2 Tables
[Countries]
| ID | Name |
| ------------ | ------------ |
| 1 | Japan |
| 2 | Egypt |
| 3 | France |
| 4 | England |
| 5 | Canada |
[VisitedCountries]
UserID | CountryID |
---|---|
1 | 2 |
1 | 3 |
1 | 5 |
2 | 4 |
3 | 1 |
3 | 5 |
I'd like to have a list of all the items in the [Countries] Table + 1 additional column with the UserID as the WHERE statement. Like so :
[ExpectedResult] (WHERE UserID = 1)
| UID | CID | CName |
| ------------ | ------------ | ------------ |
| Null | 1 | Japan |
| 1 | 2 | Egypt |
| 1 | 3 | France |
| Null | 4 | England |
| 1 | 5 | Canada |
I tried multiple different JOIN statements, but i still only get the lines where UID is not null only :
[WRONGResult] (WHERE UserID = 1)
| UID | CID | CName |
| ------------ | ------------ | ------------ |
| 1 | 2 | Egypt |
| 1 | 3 | France |
| 1 | 5 | Canada |
SELECT c.*, vc.UID
FROM [Countries] AS c
JOIN [VisitedCountries] AS vc ON vc.CID = c.ID
WHERE vc.UID = '1'
UNION ALL did not work either, it just returns 2 tables as a list, not joined.
Care to help or point me to the right documentation / direction?
3条答案
按热度按时间dced5bon1#
You simply need to outer join your tables and note the conditions are part of the joining criteria, not a filtering criteria as you still require all rows:
lhcgjxsq2#
SQL Fiddle
MySQL 5.6 Schema Setup:
Query 1:
Results:
Query 2:
Results:
kgsdhlau3#
Move the condition on left joined table column from WHERE into ON clause of LEFT JOIN