MySQL Select multiple tables but return 1 row

Question

I have a database with multiple tables all tied by an ID. The tables would look something like:

TableA:
    user_id
    user_firstname
    user_lastname

TableB:
    user_id
    exam_result
    date_taken

Table A will only ever contain a single row (unique User_ID) but Table B can contain multiple rows per User_ID. Something like this:

TableA:
    user_id = 1
    user_firstname = blah
    user_lastname = blah

    user_id = 2
    user_firstname = blah
    user_lastname = blah

TableB:
    user_id = 1
    exam_result = F
    date_taken = somedate

    user_id = 1
    exam_result = E
    date_taken = somedate

    user_id = 1
    exam_result = U
    date_taken = somedate

    user_id = 2
    exam_result = A
    date_taken = somedate

    user_id = 2
    exam_result = A
    date_taken = somedate

My question is how can I get the data out of both tables but only ever return 1 row per unique ID... I think its called a Nested Select.

If I could get the results to display something like:

user_id1, user_first_name1, user_lastname1, (F, somedate, E, somedate, U, somedate)
user_id2, user_first_name2, user_lastname2, (A, somedate, A, somedate)

Hopefully it makes sense.

Thanks in advance.

From the top of my head, this is not possible. Explain us what are you trying to archieve so we can suggest you better ways of doing it. — Ricardo Souza
– Ricardo Souza, Commented Feb 26, 2013 at 19:46

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

I think you're looking for GROUP_CONCAT:

SELECT A.User_Id, A.user_firstname, A.user_lastname,
  GROUP_CONCAT(CONCAT(B.Exam_Result,',',B.Date_Taken))
FROM TableA A
  LEFT JOIN TableB B ON A.User_Id = B.User_Id
GROUP BY A.User_Id

SQL Fiddle Demo

If you want parentheses around the result, use another CONCAT:

CONCAT('(',GROUP_CONCAT(CONCAT(B.Exam_Result,',',B.Date_Taken)),')')

EDIT:

I've updated the answer to use a LEFT JOIN instead of an INNER JOIN to handle NULL values from TableB.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 26, 2013 at 19:38

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ricardo Souza Over a year ago

He wants data from 2 columns for each row to be in the list.

gen_Eric Over a year ago

@rcdmk: This answer should give that result.

jaminben Over a year ago

@sgeddes thanks for that... it looks good. I will play with it and see what happens with my real data.

jaminben Over a year ago

@sgeddes that works but only for data which has an ID in both tables... some of my data has an ID in table A but there are no rows in for that ID in table B. Anyway it can display a null for those ID's?

jaminben Over a year ago

It actually combines all data together into one row: sqlfiddle.com/#!2/9cba4/1

|

Collectives™ on Stack Overflow

MySQL Select multiple tables but return 1 row

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related