Select within select? 1

[lifelineamerica]

Let me explain myself as best as I can I think I need a select within a select. This is for a school bus run. I want to pull from a db all kids I am picking up at each address.

Table 1: Parents - Parent_id, Address, etc.
Table 2: Kids - Parent_id, Kid_id, ect

The following will output on each row the address and the total number of kids for that address:

SELECT COUNT(Kids.Kid_id) as num_students, address
FROM Kids
LEFT JOIN Parents ON Parents.Parent_id=Kids.Parent_id
GROUP BY Parents.address

The following will output the address but only the first kid id in that row:

SELECT Kids.Kid_id as num_students, address
FROM Kids
LEFT JOIN Parents ON Parents.Parent_id=Kids.Parent_id
GROUP BY Parents.address

I want to get all the kid id's for each (location) row as well.

 

[KarveR]

for the second query, dont group by the address then ?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[lifelineamerica]

Thanks , but I need it grouped so that for each row the address will come up once, whereas the kids for that address would be multiple.

Example of output I am craving:

Row 1: [31 Bromwell Street] - (Anthony, John, Kathy, Mary)

Row 2: [49 Broom Drive] - (Colin, Johnathan, Sally)

 

[r937]

when you say FROM X LEFT JOIN Y this means you are looking for all X rows, whether or not a given X row has a matching Y row

so in your query, where you have FROM Kids LEFT JOIN Parents, you are looking for all Kids, whether or not a given Kid has a matching Parent, which doesn't make sense

so just use INNER JOIN

as for your requirement, you are lucky that mysql has a special function (many database systems don't) just for this type of result

select Parents.address
     , [b]group_concat[/b](Kids.Kid_id) as list_of_kids
  from Parents
inner
  join Kids 
    on Kids.Parent_id = Parents.Parent_id
group 
    by Parents.address

r937.com | rudy.ca

 

[KarveR]

Learn something new every day, group_concat - how did I ever live without it, a star for you r937

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[lifelineamerica]

Thanks a lot. Tried, but something is bothering MySQL, and it doesn't like the new query.

Here is the full query (with the real table names and fields that I had left out in my previuos post for simplicity sake)

SELECT bnai_parents.address, GROUP_CONCAT(bnai_kids.Kid_id) AS list_of_kids
FROM bnai_parents
INNER JOIN bnai_kids ON bnai_kids.Parent_id=bnai_parents.Parent_id
GROUP BY bnai_parents.address

And the error is:


MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(bnai_kids.Kid_id) AS list_of_kids
from bnai_parents inner joi

 

[r937]

well, there can be only one reason -- you're on one of the "dinosaur" versions of mysql

time to upgrade



r937.com | rudy.ca