How to write SQL query for this Scenario?

user_8011392 0
Viewed 117

An interviewer asked me this question, You are given two tables with columns as L1,T1 for table1 while T,Name for table2. Write a SQL query to get the desired result. enter image description here

I'm still stuck on how to write the query to get the desired output.

Used this, SELECT table1.L1, table2.Name FROM table1 INNER JOIN table2 on table1.T1 = table2.T; but this way it won't CONCAT the output for Name wrt L1.

Thanks for helping in advance.

mysql
4 Answers

You need an aggegation function as GROUP_CONCAT for obtain the comma separated result for name 

  SELECT table1.L1,GROUP_CONCAT( table2.Name ORDER BY table2.Name ASC SEPARATOR ',')
  FROM table1 
  INNER JOIN table2 on table1.T1 = table2.T
  GROUP BY table1.L1 
  ORDER BY FIELD(table1.L1,'X','Y','Z')

withou aggegation function you get result on separated rows .. instead with group by an group_concat you obtain all the name related to a one l1 on the same row

user_35223120

You join the two tables using JOIN (Inner Join). Then use GROUP BY to get aggregated data, and then eventually utilize GROUP_CONCAT function to achieve comma separated names. See below:

SELECT table1.l1, GROUP_CONCAT(table2.name) 
FROM table1 
JOIN table2 ON table1.t1 = table2.t 
GROUP BY table1.l1
user_24693080

use this query

SELECT table1.L1, GROUP_CONCAT(table2.name separator ', ')
FROM table1 AS table1
INNER JOIN table2 as table2 ON table1.T1 = table2.t
GROUP BY table1.L1

SQL fiddle here to test it live http://sqlfiddle.com/#!9/3ac3a3/1/0

user_32283400

This sql will get the result.

SELECT L1, GROUP_CONCAT(name) from Table1 INNER JOIN Table2 on T1 = T GROUP BY L1

user_93688220
Related