Not getting the results I want from multiple joins

[LegoAddict]

Hi there,

I'm not very good at SQL and this is something that has always bothered me.

Here is my code:

select Table1.WholeCompany, Table1.BlockingDate, Table2.Description, Table3.CompanyName, Table4.FirstName+', '+ Table4.LastName+'. '+ Table5.Role AS UserResponsible, Table1.BlockingNotes, Table6.FirstName, Table6.LastName from Table1
LEFT JOIN Table3 on Table1.RedGUID = Table3.GUID
LEFT JOIN Table2 ON Table1.BlueGUID = Table2.GUID
LEFT JOIN Table1Detail ON Table1.GUID = Table1Detail.GreenGUID
LEFT JOIN Table6 ON Table1Detail.PurpleGUID = Table6.GUID
LEFT JOIN Table2Responsibility ON Table2Responsibility.BlueGUID = Table2.GUID
LEFT JOIN Table4 ON Table2Responsibility.UserGUID = Table4.GUID
LEFT JOIN Table5 ON Table2Responsibility.RoleGUID = Table5.GUID
WHERE Table1.BlockingDate > GetDate() AND Table2Responsibility.RoleGUID = '4B393787-A78C-4149-9B91-28EADA28E48D'

and you can find my results below and the results I actually want below that.

The RoleGUID = Partner.

Not sure what type of joins or other SQL functions I need to use in order to get my desired results. Any help/hints would be greatly appreciated! I included 3 companies, 2 of them only have one partner where as Coke has 3 partners.

P.S. Sorry for the weird names, some of it is confidential!


WholeCompany BlockingDate Description Company UserResponsibility Notes FirstName LastName
1 00:00.0 Decrption1 Pepsi John Doe. Partner BLAH BLAH BLAH NULL NULL
1 00:00.0 Decrption2 Coke Jane Doe. Partner NULL NULL NULL
1 00:00.0 Decrption2 Coke Jim Doe Partner NULL NULL NULL
1 00:00.0 Decrption2 Coke Jayne Doe Partner NULL NULL NULL
1 00:00.0 Decrption3 Dr.Pepper Jason Doe Partner NULL NULL NULL


WholeCompany BlockingDate Description Company UserResponsibility UserResponsibility2 UserResponsibility3 Notes FirstName LastName
1 00:00.0 Description1 Pepsi John Doe. Partner BLAH BLAH BALH NULL NULL
1 00:00.0 Description2 Coke Jane Doe Partner Jim Doe. Partner Jayne Doe. Partner NULL NULL NULL
1 00:00.0 Description3 Dr. Pepper Jason Doe Partner NULL NULL NULL

Thanks!

 

[markros]

I think you may want to change UserResponsible definition from
Table4.FirstName+', '+ Table4.LastName+'. '+ Table5.Role

to

coalesce(Table4.FirstName+', ','')+ coalesce(Table4.LastName+'. ','') + coalesce(Table5.Role,'')

You may also try adding DISTINCT keyword to your select statement to remove complete duplicates.

PluralSight Learning Library

 

[drlex]

Should your filter

WHERE ... AND Table2Responsibility.RoleGUID = '4B393787-A78C-4149-9B91-28EADA28E48D'

have the choice of Table2Responsibility.RoleGUID being NULL?


soi là, soi carré