Another LEFT JOIN problem!

MySQL

[tata668]

Let's say I have some users, books and CDs. Each book and each CD belows to a particular user:

my tables:

--------
[testuser]
id: int
--------
[testbook]
id: int
userid: int
--------
[testcd]
id: int
userid: int
--------

And let's say I have two users:
- id = 1
- id = 2

two books:
- id = 1, userid = 1
- id = 2, userid = 1

and two CDs:
- id = 1, userid = 1
- id = 2, userid = 1

Both books and both CDs below to user 1. User 2 has no book and no CD.

I want to list all the users, the number of books they have and the number of CDs they have. I want the users with no book and no CD to be listed too.

This doesn't work:

SELECT testuser.id,
SUM(testuser.id = testbook.userid) as nbrBooks,
SUM(testuser.id = testcd.userid) as nbrCds 
FROM testuser
LEFT JOIN testbook ON testuser.id = testbook.userid
LEFT JOIN testcd ON testuser.id = testcd .userid
GROUP BY testuser.id

It returns:
userid ¦ nbrBooks ¦ nbrCds
1 ¦ 4 ¦ 4
2 ¦ NULL ¦ NULL

But user1 doesn't have 4 books and 4 Cds!
I think the problem is that the two LEFT JOIN join together in a way or another.

What would be the correct way to achieve what I'm trying to do?

Thanks for the help!

[bibby]

Just a quick note before I try this problem out for myself ( i need to learn joins too). But I've gotten by using PHP to do all that:

// select users;
// while (users){
// count books
// count cds
// }

[bibby]

Well , I got nothing ; other than you should probably by looking for a COUNT() rather than SUM() . Anything that I try returns a count of all rows, including the users. I can do a single book count, or cd count, but not both. I'm weak.
Seriously, I'd PHP this:

echo"<table><tr>
<td>User</td>
<td>Number of Books</td>
<td>Number of CDs</td></tr>";
$ppl=mysql_query("select * from testuser order by id");
while ($user=mysql_query($ppl)){

$countbooks=mysql_query("select count(id) as numbooks from testbook where userid='$user[id]'");
$ihave=mysql_fetch_assoc($countbooks);
$books=$ihave[numbooks];

$countCDs=mysql_query("select count(id) as numcds from testcd where userid='$user[id]'");
$ihave=mysql_fetch_assoc($countCDs);
$cds=$ihave[numcds];

echo"<tr>
<td>$user[id]</td>
<td>$books</td>
<td>$cds</td></tr>";
}
echo "</table>";

--------------------
If you wish to have totals, keep a $tally and add to it each pass.
If you want to be able to sort, I'd probably keep a temp database that stashes these counts , and you can draw from that:
$SortByBooks="select * from tempDB order by numbooks desc";

Even with a few hundred users or media items, it won't take that long ( and you'd be done today).

[tata668]

bibby, thanks for your reply.

I'm not sure I like the idea to get all the users and then have to do 2 new searches for each of them.. I think this won't be efficient.

I found a way to do it using only one query but it requires subqueries (I'm using Mysql 4.1):

SELECT testuser.id,

(
SELECT COUNT( * )
FROM testbook
WHERE testbook.userid = testuser.id
) AS nbrBooks,

(
SELECT COUNT( * )
FROM testcd
WHERE testcd.userid = testuser.id
) AS nbrCds

FROM testuser

That works, but I don't like this solution because in fact my real query is more like this:

SELECT testuser.id,

(
SELECT SUM( testbook.id < 5)
FROM testbook
WHERE testbook.userid = testuser.id
AND [*** some other criterias ***]
GROUP BY testbook.id
) AS nbrBooksBelow5,

(
SELECT SUM( testbook.id < 10)
FROM testbook
WHERE testbook.userid = testuser.id
AND [*** some other criterias ***]
GROUP BY testbook.id
) AS nbrBooksBelow10,

(
SELECT COUNT( * )
FROM testcd
WHERE testcd.userid = testuser.id
) AS nbrCds

FROM testuser

Even if the exact same criterias are used for both subqueries related to books, Mysql will probably have to redo a lot of work for each subquery because they are in a different SELECT!

Does what I say make sense?

Any other idea? Thanks again for any help...

[AlexK]

tata668, your LEFT JOINs are OK, it is simply that some of them are empty (user with id=2 does not exist in table testbook nor testcd, but the LEFT JOIN will still return a row for this user--as the user exists in table testuser--but it will be an empty row). Thus, if you COUNT() the ids it will be 4 (2 with info, 2 empty). To exlude the NULL fields use:

LEFT JOIN testcd ON testuser.id = testcd .userid WHERE testcd .userid IS NOT NULL

and you will get the correct count.

[tata668]

Thanks for your reply AlexK but what you suggest doesn't help me!

This:

SELECT testuser.id,
SUM(testuser.id = testbook.userid) as nbrBooks,
SUM(testuser.id = testcd.userid) as nbrCds
FROM testuser
LEFT JOIN testbook ON testuser.id = testbook.userid
LEFT JOIN testcd ON testuser.id = testcd .userid
WHERE testcd .userid IS NOT NULL
GROUP BY testuser.id

returns:
id ¦ nbrBooks ¦ nbrCds
1 ¦ 4 ¦ 4

But user #1, does not have 4 books and 4 cds! He has 2 books and 2 cds. Furthermore, I want user #2 to be listed, even if he doesn't have any book or cd!

I'm pretty sure the problem is that both LEFT JOIN are joining together were they shouldn't!

Any other idea?

(By the way, I'm sorry for my poor english. I just realised that I wrote "belows" instead of "belongs")