mySQL join and count

[WatsonN]

Hello all, I have to mysql tables and I want to join them and count the amount of rows in one.

I have two tables YBK_Login and YBK_Ads, what I want to do is join them together and select a user's ID from YBK_Login and see how many entries that user has posted in YBK_Ads.

The common field from YBK_Login is ID and in YBK_Ads is UID.

Also, if you could explain so I can learn from this that would be much appreciated.

 

If you need me to be more specific please let me know.

 

 

Thank you in advanced.

[kickstart]

Hi

 

Something like this

 

SELECT a.UID, COUNT(b.id)
FROM YBK_Login a
LEFT OUTER JOIN YBK_Ads b
ON a.UID = b.UID
GROUP BY a.UID

 

Basically joins the 2 tables together. Uses a left join so that you will still get a row returned when there is no matching row on the ads table. Then counts the occurances of the primary key on the 2nd table (COUNT used like this will ignore nulls, so anyone with no ads will have 0 here, using COUNT(*) would count the rows and would give 1). Groups by the shared key field to get the count per occurance of that key.

 

If you want more fields from the first table then add them to both the SELECT and the GROUP BY.

 

All the best

 

Keith

[WatsonN]

@kickstart

Thank you for this, it is exactly what I needed.

Also, thank you for taking the time to write and explain it. Hopefully I will remember this and expand on it in the future.