SQL query select multiple rows in one field based on id

Question

I have this query:

SELECT Id,
SUBSTRING(
(SELECT ',' + lts.Selection
FROM LiveTrainingSelections lts 
INNER JOIN LiveTraining lt ON lt.Id = lts.LiveTrainingId
WHERE lts.SelectionType = 'Session Format' and lts.LiveTrainingId = lt.Id
FOR XML PATH('')),2,200000) AS SelectionDetails
 FROM LiveTraining lt
JOIN LiveTrainingSelections lts 
ON lt.Id = lts.LiveTrainingId

This is working almost properly, the only thing is that I'm getting all the records concatenated in one field, but I want to get all the fields specific for that Id, please check the screenshot

As you can see in the first result all the values are concatenated and the LiveTrainingId is ignored, but what I want to get is only the three values specific for that LiveTrainingId concatenated.

lc. · Accepted Answer · 2016-12-22 09:53:52Z

1

Those joins are not right. The alias lt in your subquery is overriding the alias with the same name from the outer FROM clause, which is what you appear to be truly wanting to match to. Renaming the aliases so they are unique - note the 2's - should work:

SELECT Id,
SUBSTRING(
(SELECT ',' + lts2.Selection
FROM LiveTrainingSelections lts2 
INNER JOIN LiveTraining lt2 ON lt2.Id = lts2.LiveTrainingId
WHERE lts2.SelectionType = 'Session Format' and lts2.LiveTrainingId = lt.Id
FOR XML PATH('')),2,200000) AS SelectionDetails
 FROM LiveTraining lt
JOIN LiveTrainingSelections lts 
ON lt.Id = lts.LiveTrainingId

But from your query I am guessing the joins are actually superfluous and you simply want to select from LiveTraining in the outer query and LiveTrainingSelections in the inner query:

SELECT Id,
SUBSTRING(
(SELECT ',' + lts.Selection
FROM LiveTrainingSelections lts 
WHERE lts.SelectionType = 'Session Format' and lts.LiveTrainingId = lt.Id
FOR XML PATH('')),2,200000) AS SelectionDetails
FROM LiveTraining lt

answered Dec 22, 2016 at 9:53

lc.

117k21 gold badges164 silver badges190 bronze badges

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

1 Comment

Laziale Over a year ago

Thanks that is correct! I'll select your answer in 6 minutes

Shushil Bohara · Accepted Answer · 2016-12-22 10:18:09Z

0

I think we can simply do it using STUFF please try, if it gives you the desired output:

SELECT ltd.Id,
STUFF((SELECT ',' + lta.Selection FROM FROM LiveTrainingSelections lta
        WHERE lt.Id = lta.LiveTrainingId 
        AND lta.SelectionType = 'Session Format' 
                                 FOR XML PATH('')),1,1,'') AS SelectionDetails,            
FROM LiveTraining lt
INNER JOIN LiveTrainingSelections lts ON lt.Id = lts.LiveTrainingId
GROUP BY ltd.Id

answered Dec 22, 2016 at 10:18

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Comments

Dinesh Singh · Accepted Answer · 2016-12-22 10:25:09Z

0

Please try below sql:

SELECT  It.Id, STUFF((SELECT  ',' + lts.Selection
            FROM LiveTrainingSelections lts 
            WHERE  lts.LiveTrainingId = lt.Id
            ORDER BY lts.LiveTrainingId
        FOR XML PATH('')), 1, 1, '') AS listStr
FROM LiveTraining lt
WHERE exists(
SELECT 1 FROM LiveTrainingSelections lts2
WHERE lt.Id = lts2.LiveTrainingId
and lts2.SelectionType = 'Session Format'
)
GROUP BY lt.Id

answered Dec 22, 2016 at 10:25

Dinesh Singh

2101 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQL query select multiple rows in one field based on id

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related