Subquery problems
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) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery problems
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) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 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
Re: Subquery problems
Joppe A wrote: 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) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 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! Try something like this: SELECT ID,NAME,COUNT(*) FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID GROUP BY ID,NAME -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery problems
Roger Baklund wrote: Try something like this: SELECT ID,NAME,COUNT(*) FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID GROUP BY ID,NAME Nope, sorry, that won't work, ID exists in both tables thus it must be prefixed with table name or alias: SELECT tableA.ID,... GROUP BY tableA.ID,... But Shawn Green allready gave you the answer, so I suppose you are in control of things. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
