How many....

pablovicente

I've got two tables, user, sales.
user has an user id, and sales has that same user id so i can relate the sales with the user.

Now i want to know how many sales has each user, but the problem is when someone has 0 sales this user don't apear in the result, i want them to aper with 0 so i get this:

user + 30/10 + 31/10 +

John + 10 + 0 +

Dana + 0 + 0 +

Paul + 1 + 1 +

how do i get that?!!!!!!!
what query can make something like that?
I've tried hard but i failed.
Thanks in advance.

rem

What's your SGBD ? Mysql ?

pablovicente

mysql

Lars_Berg

Use an outer join instead. Show your current query.

rem

Use LEFT JOIN on the user Id.

Say that the tables are,

[COLOR=red][B]myuser[/B][/COLOR]
Field	Type		Key	Default	Extra	
-----	----		---	-------	-----	
iuser	int(11)		PRI	NULL	auto_increment	
luser	varchar(64)		NULL

[COLOR=red][B]mysales[/B][/COLOR]
Field	Type		Key	Default	Extra	
-----	----		---	-------	-----	
iBuyer	int(11)			0		
date	date			0000-00-00		
what	varchar(64)

the query 'ill be

SELECT iuser, luser, IFNULL(what, 'n/a'), IFNULL(date, 'n/a')
FROM myuser LEFT JOIN mysales ON iBuyer = iuser;

which produces

iuser	luser	ifnull(what, 'n/a')	ifnull(date, 'n/a')	
-----	-----	-------------------	-------------------	
3	John	candies			2002-10-24	
2	Dana	n/a			n/a	
1	Paul	potatoes		2002-10-30	
1	Paul	Salad			2002-10-31

(4 row(s) affecteds

pablovicente

........ and was just testing left joins thanks a lot rem!!!!