SQL join to many rows

Question

I have a sql Query with a lot of queries.

But i will try to make it simple

Table.Result

Id
Result

Table.ResultGender

ResultID
GenderID

Table.Gender

Id
Gender

One result can have more than one gender. So want a result like this

Result             |  Gender

Some result        | Female

Another result     | Female, male

But i'm getting

Result             |  Gender

Some result        | Female

Another result     | male

Another result     | Female

Query:

    SELECT Gender.Name , Result.Result     
    FROM Gender 
LEFT OUTER JOIN ResultGender ON Gender.Id = ResultGender.GenderId  
LEFT OUTER JOIN Result ON ResultGender.ResultId = Result.Id

UPDATE

I have tried this

    SELECT Gender.Name , Result.Result  ,
    STUFF((SELECT  ',' + Name
    FROM            Gender
     WHERE        (Id = Gender_1.Id) FOR XML PATH(''))as varchar(max)) AS test
    FROM            Gender AS Gender_1 
LEFT OUTER JOIN ResultGender ON Gender_1.Id = ResultGender.GenderId 
LEFT OUTER JOIN Result ON ResultGender.ResultId = Result.Id

AND THIS in SQL manager

    SELECT Gender.Name , Result.Result  ,
    CAST((SELECT  ',' + Name
    FROM            Gender
     WHERE        (Id = Gender_1.Id) FOR XML PATH(''))as varchar(max)) AS test
    FROM            Gender AS Gender_1 
LEFT OUTER JOIN ResultGender ON Gender_1.Id = ResultGender.GenderId 
LEFT OUTER JOIN Result ON ResultGender.ResultId = Result.Id

BOTH are trying to save a RPT file

Depending on the Database you are using you could use a aggregation function like [listagg](http://docs.oracle.com/database/121/SQLRF/functions100.htm) from oracle or [group_concat](http://stackoverflow.com/questions/9456380/aggregate-function-in-mysql-list-like-listagg-in-oracle) from mysql. You simply need a group by with it and you will get the result — SomeJavaGuy, Aug 24 '15 at 13:52
So you want to aggregate your data such as to get a comma-separated Gender list per Result? What is your DBMS? Some offer a function such as LISTAGG and GROUP_CONCAT for this , others don't. EDIT: Well Kevin was a lot faster :-) — Thorsten Kettner, Aug 24 '15 at 13:52
Please remove all those join tags and tag your DBMS (MySQL, SQL Server, Oracle or whatever) instead. — Thorsten Kettner, Aug 24 '15 at 14:03
I ended up creating a small web application to handle it, i needed alot of joins so it would be easier — AWR, Sep 10 '15 at 09:54

score 0 · Answer 1 · answered Aug 24 '15 at 13:52

0

Try in following:

DECLARE @Result VARCHAR(8000) 
SELECT Gender.Name, @Result = COALESCE(@Result + ', ', '') + Result.Result 
FROM Gender 
JOIN Result ON Gender.Id = Result.Id 
JOIN ResultGender ON Gender.Id = ResultGender.GenderId 
     AND StudyResult.Id = ResultGender.ResultId

answered Aug 24 '15 at 13:52

Stanislovas Kalašnikovas

9,203
4
32
59

I get this error _Msg 141, Level 15, State 1, Line 2 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations._ – AWR Aug 24 '15 at 14:02

score 0 · Answer 2 · edited May 23 '17 at 12:14

You are joining incorrectly. Gender.Id is the ID of a Gender record, and Result.Id is the ID of a Result record. Hence Gender.Id = Result.Id makes no sense. ResultGender is the brige table you need for the join: Gender -> GenderResult -> Result.

As to your actual request: Group by Result in order to get one record per Result. Get your Gender list with an appropriate function (I'm showing it here with MySQL's GROUP_CONCAT.)

select 
  r.result     
  group_concat(g.name)
from result r
inner join resultgender rg on rg.resultid = r.id
inner join gender g on g.id = rg.genderid
group by r.result;

Unfortunately SQL Server doesn't provide such function as GROUP_CONCAT. It needs some fumbling with XML PATH and STUFF instead. See here: How to make a query with group_concat in sql server.

In case you have Results without a Gender, use outer joins instead:

left join resultgender rg on rg.resultid = r.id
left join gender g on g.id = rg.genderid

SQL join to many rows

2 Answers2