SQL syntax help, conditional AND

Question

Been coding for 48 hours straight and am banging my head against the wall here. Please help me with this small issue.

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 (which is OK).

I want the query to give me all the other info (like, return NULL for City and Country) for this user even if one or both those fields are NULL. How to make the AND-parts conditional?

I hope I'm making myself clear in my fogginess.

"Been coding for 48 hours straight " - there's your problem! ;) — Mitch Wheat
– Mitch Wheat, Commented Jul 27, 2011 at 23:50
I'm not sure of the exact syntax for MySql, but it sounds like you need to do outer joins on the tables that may have null results. — Tim
– Tim, Commented Jul 27, 2011 at 23:50
See "How to keep being productive when you are tired?" and don't be so silly — gbn
– gbn, Commented Jul 27, 2011 at 23:52
Haha @Mitch, gbn I think you guys are on to the root of the problem! — Mattis
– Mattis, Commented Jul 28, 2011 at 0:27

Martin Smith · Accepted Answer · 2011-07-27 23:50:09Z

4

I think you need a couple of OUTER joins if I have understood 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

answered Jul 27, 2011 at 23:50

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mattis Over a year ago

Thanks Martin, I flipped a coin on who got the correct answer tag :)

Paul Sonier · Accepted Answer · 2011-07-27 23:59:07Z

3

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

So your FROM 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

The LEFT JOIN is an OUTER join; it will join across the tables where the leftmost (hence the LEFT in the JOIN) term in the JOIN (i.e., the first one that appears) has an entry but the other table does not. OUTER JOINs are useful for these situations where you don't necessarily have data entries in all the tables for what you want from a query; they can be confusing at first, but they become very important to using SQL well.

edited Jul 27, 2011 at 23:59

answered Jul 27, 2011 at 23:50

Paul Sonier

39.6k3 gold badges81 silver badges118 bronze badges

Collectives™ on Stack Overflow

SQL syntax help, conditional AND

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related