How do i count the senders with no gifts?

MYSQL Select Query

[Alina]

Please could you help me with my MYSQL query? I have the following 2 tables

1. senders with sender_id
2. gifts with gift_id and sender_id

What i am trying to do is count how many gifts bought per sender using both tables. But i cannot seem to retrieve
those senders that have not sent any gifts at all. My PHP MYSQL query looks like the following:

$stmt = "SELECT tx.*, COUNT(*) AS cnt
FROM table_senders AS tx,
table_gifts AS ty USE INDEX (index_sender_and_gift)
WHERE tx.sender_id=ty.sender_id
GROUP BY ty.sender_id ORDER BY cnt DESC LIMIT $st, $end";

This works fine except it does not retrieve the senders that have not sent any gifts. Do you know how i could modify the above statement so that it corporates those senders that have sent 0 gifts? - i.e the people that would exist in the sender table but not in the gift table.

Many thanks for your time

Alina

[txbakers]

Hi what you need is a "left outer join"

SELECT tx.*, COUNT(*) AS cnt
FROM senders AS tx left outer join gifts AS ty ON tx.sender_id=ty.sender_id
GROUP BY ty.sender_id ORDER BY cnt DESC LIMIT $st

that will bring back every sender whether they have sent a gift or not.

[Alina]

Hi txbakers
Many thanks for you help. I have just tried this and the count for the senders with no gifts is comming out as 1 instead of 0. But it is working better - do you have any suggestions on how i could refine the select statement that you have just sent me so that the result comes out as 0 instead of 1?

Many thanks again

Alina

[txbakers]

I would put the count in a separate query, or set the query type to allow forward and back reading. In ASP, that allows me to do a "RecordCount" property on the query to get the number of records.

I'm not sure what it is called in PhP, but someone does.

[coopster]

You can check for a NULL value in one of the right hand table's columns that would not normally have a NULL value. In a LEFT JOIN any column from the secondary (right hand) table with no matching rows will return NULL values.