MySQL problem..

[TomaHawk]

Put very simply I have two tables:

- users
- briefs

I am trying to SELECT several fields from the 'users' table but within the WHERE clause the 'users.userID = briefs.userID'.

Here's my problem; that users may have a row in the users table but not necessarily in the briefs table. So if there is no corresponding row to be found where the two ID's match I obviously get no row returned.

So how do I select all users with AND without an entry in the briefs table (or for example displaying '0' for those without an entry in the briefs table) from a query?

Currently I have:
---
SELECT users.userID, users.prefix, users.firstname, users.surname, users.lastlogin, COUNT(briefs.briefID) AS briefs
FROM users, briefs
WHERE (users.userID = briefs.userID)
GROUP BY briefs.userID
ORDER BY users.surname
---

So I realise the WHERE clause is causing those rows without a corresponding entry in the BRIEFS table to not be displayed. So my question is how to return all those values currently being returned with the SQL above; plus displaying '0' (zero) for those without a corresponding row in the briefs table.

I've been looking at this and thinking I may need to use a CASE or IF function; but thought there may be a simpler way I'm just missing!

Many thanks,
Lewis.

 

[SiD the Turtle]

You'll need to use a join, a left join in this case:

http://www.w3schools.com/sql/sql_join.asp
http://en.wikipedia.org/wiki/Join_(SQL)

SELECT users.userID, users.prefix, users.firstname, users.surname, users.lastlogin, COUNT(briefs.briefID) AS briefs
FROM users LEFT JOIN briefs ON users.userID = briefs.userID
GROUP BY briefs.userID
ORDER BY users.surname

This will take whatever is on the left of the join, in this case users, and try and match it to the right side of the join, briefs. If there is no match null is returned. I think that is the right syntax above, though it's too early in the morning for my brain to be completely engaged

Hope that helps.

Edit: I just tested that code out and it seems to do the trick.

 

[TomaHawk]

You'll need to use a join, a left join in this case:

http://www.w3schools.com/sql/sql_join.asp
http://en.wikipedia.org/wiki/Join_(SQL)

SELECT users.userID, users.prefix, users.firstname, users.surname, users.lastlogin, COUNT(briefs.briefID) AS briefs
FROM users LEFT JOIN briefs ON users.userID = briefs.userID
GROUP BY briefs.userID
ORDER BY users.surname

This will take whatever is on the left of the join, in this case users, and try and match it to the right side of the join, briefs. If there is no match null is returned. I think that is the right syntax above, though it's too early in the morning for my brain to be completely engaged

Hope that helps.

...doh! Course joins hah.
I was trying to make something more complicated than it need be; works a charm many thanks, appreciated!

 

[SiD the Turtle]

No probs

 

[Fourstar]

Rather than rely on outer joins I recommend adding a "commando" entry to your briefs table