SQL syntax help, conditional AND

Question

There was coding 48 hours in a row, and I knock my head on the wall here. Please help me with this little problem.

My SQL query is this:

SELECT u.Firstname, u.Lastname, u.Rep, u.Email, u.Password, u.Gender, u.Level, u.Birthday, u.Achievements, u.Height, u.Unit, u.cityid, u.countryid, r.RegDate, ci.Name AS City, co.Name AS Country FROM Users u, Registry r, Cities ci, Countries co WHERE u.id = 1 AND r.uid = u.id AND u.cityid = ci.id AND u.countryid = co.id LIMIT 1 

My problem is that I just noticed that sometimes Users.cityid and Users.countryid are NULL (this is normal).

I want the request to provide me with all the information (e.g. return NULL for City and Country ) for this user, even if one or both of these fields is NULL . How to make AND parts conditional?

I hope I clarify my nebula.

Answer 1

You need to use LEFT JOIN in your tables instead of using WHERE .

So, your OT turns into:

 FROM Users u JOIN Registry r on u.id = r.uid LEFT JOIN Cities ci ON u.cityid = ci.id LEFT JOIN Countries co ON u.countryid = co.id WHERE u.id = 1 LIMIT 1 

LEFT JOIN is an OUTER join; it will join through tables where the leftmost (hence LEFT in JOIN ) member in the JOIN (i.e. the first that appears) has a record, but not in another table. OUTER JOIN are useful for situations where you do not necessarily have data records in all tables for what you want from the query; they may be confusing at first, but they are very important for good use of SQL.

Answer 2

I think you will need some OUTER affiliates if I understand your situation correctly.

 SELECT ... FROM Users u INNER JOIN Registry r ON r.uid = u.id LEFT JOIN Cities ci ON u.cityid = ci.id LEFT JOIN Countries co ON u.countryid = co.id WHERE u.id = 1