"Joppe A" <[EMAIL PROTECTED]> wrote on 02/17/2005 09:45:31 AM:
> hello all, > I am a newbe working with MySQL. > I have a problem that I can't figure out how to do. > > I have 2 tables that I want to take out data from, the tables looks > like below. > > tableA > ====== > ID VARCHAR(12) > NAME VARCHAR(255) > CREATED TIMESTAMP > > tableB > ====== > USER_ID VARCHAR(12) > ID VARCHAR(12) > SUB_DATE TIMESTAMP > CREATED TIMESTAMP > > Okay here is how it looks in them. > > tableA > > ID NAME CREATED > 01 boat 20050117103245 > 02 car 20050213133418 > 03 mc 20050214015902 > 04 bike 20050217081232 > > tableB > > USER_ID ID SUB_DATE CREATED > 1000 02 20050214135412 20050213133418 > 1001 04 20050215143410 20050213124415 > 1002 04 20050213133418 20050214133418 > 1003 03 20050213133418 20050213133418 > > Now to my problem, I want to get out the ID,NAME from tableA and > then from tableB get the "count" of how many that that choosen the > specific alternative in the ID column. The problem is also that I > only want to specify the ID once in the sql-question. > > My answer I want to have from my question should be something like this: > > 01 boat 0 > 02 car 1 > 03 mc 1 > 04 bike 2 > > Would be greatful if somebody could help! > > -- > ___________________________________________________________ > Sign-up for Ads Free at Mail.com > http://promo.mail.com/adsfreejump.htm > Here's one way to do it (there are others) but this works on older server versions because it doesn't use a subquery. SELECT A.ID, A.NAME, COUNT(B.USER_ID) FROM tableA A LEFT JOIN tableB B ON B.ID = A.ID GROUP BY a.ID, A.NAME; Suggested additional reading: http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/join.html http://dev.mysql.com/doc/mysql/en/group-by-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
