CODEWITHSUNDEEP
mysql
Sandeep Gupta
Sandeep Gupta

Reputation: 134

How to write SQL query for this Scenario?

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.

Upvotes: 3

Views: 181

Answers (4)

Harrison Ifeanyichukwu
Harrison Ifeanyichukwu

Reputation: 292

This sql will get the result.

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 4

LahiruTM
LahiruTM

Reputation: 648

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

Upvotes: 2

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

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

Upvotes: 2

Related Questions