Returning 1 too many relationship in one row

Question

Returning 1 too many relationship in one row

smw147

SSCommitted

Points: 1544
More actions
April 7, 2015 at 8:46 am

#150334

Hi all. Not sure how to explain this, and not sure if this is the correct place to put this question, but here goes anyway.
I have 2 tables, with the relationship between them being a 1-to-many.
TABLE 1 - Clients
ClientID - ClientName
10001 - Joe Bloggs
10002 - Jane Doe
10003 - John Doe
TABLE 2 - LinkedClients
ID - ClientID - LinkedClientID
10001 - 10001 - 10002
10002 - 10001 - 10003
There are other columns in both tables, but for the purpose of this question, these are the columns I'm interested in.
So based on the above, the client Joe Bloggs is linked to both the John Doe and Jane Doe clients. If I write a SQL to show a list of linked clients
SELECT Clients.ClientName, Clients2.ClientName AS [LinkedClient]
FROM Clients
LEFT JOIN LinkedClients ON LinkedClients.ClientID = Clients.ClientID
LEFT JOIN Clients Clients2 ON LinkedClients.LinkedCLientID = Clients2.ClientID
This will show 2 lines in the result, one for each linked client record.
ClientName - LinkedClient
Joe Bloggs - Jane Doe
Joe Blocggs - John Doe
I need a SQL statement which will show this all in one line. I first thought about aliasing the tables using another field in the LinkedClients table called "Type", but this would still have a chance of returning multiple lines as the field where the type is the same because the field isn't unique. I then thought of using a sub select or CTE and use the ROW_NUMBER function, but again, I'd need an array per main client record from the Clients table, so again I can't see how this would work.
As SQL can't be dynamic in that it can't create columns on the fly, I'm unsure how to get this to work.
Does any of this make sense? I actually don't think this is going to be possible.
Ideally I'd cap the number of columns to say 5 (maximum of 5 links per main client record), but where there are more than one link, I just can't see how I can have the columns displaying the data as I want.
ClientName - LinkedClient1 - LinkedClient2
Joe Bloggs - Jane Doe - John Doe
Regards
Steve

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Hi and welcome to the forums. This can be done, even dynamically. Take a look at the articles referenced in signature about cross tabs, especially the second one about dynamic cross tabs.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

smw147 SSCommitted Points: 1544 More actions · Answer 2

Hi all,

I tried looking at these but was unable to get it to work as I wanted. In the end I set a maximum of 10 columns, and aliased the table 10 times then used a CTE for each column item.

Working result

WITH LinkedClient1(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA01%')

,

LinkedClient2(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA02%'),

LinkedClient3(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA03%'),

LinkedClient4(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA04%'),

LinkedClient5(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA05%')

,

LinkedClient6(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA06%')

,

LinkedClient7(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA07%'),

LinkedClient8(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA08%'),

LinkedClient9(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA09%'),

LinkedClient10(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA10%')

SELECT

SubQuery.ClientRef,

SubQuery.Account_Number AS [Account Number],

SubQuery.ClientName AS [Client Name],

SubQuery.[Linked Client 1],

SubQuery.[Linked Client 1 A/C No],

SubQuery.[Linked Client 2],

SubQuery.[Linked Client 2 A/C No],

SubQuery.[Linked Client 3],

SubQuery.[Linked Client 3 A/C No],

SubQuery.[Linked Client 4],

SubQuery.[Linked Client 4 A/C No],

SubQuery.[Linked Client 5],

SubQuery.[Linked Client 5 A/C No],

SubQuery.[Linked Client 6],

SubQuery.[Linked Client 6 A/C No],

SubQuery.[Linked Client 7],

SubQuery.[Linked Client 7 A/C No],

SubQuery.[Linked Client 8],

SubQuery.[Linked Client 8 A/C No],

SubQuery.[Linked Client 9],

SubQuery.[Linked Client 9 A/C No],

SubQuery.[Linked Client 10],

SubQuery.[Linked Client 10 A/C No]

FROM

(

SELECT

Clients.ClientRef,

Clients.AccountNumber AS [Account_Number],

Clients.ReportName AS [ClientName],

LinkedClient1.LinkDesc+' - '+LinkedClient1.LinkedClientName+' - REL:'+LinkedClient1.LinkRel+' - Type: '+LinkedClient1.LinkType

AS [Linked Client 1],

LinkedClient1.LinkACNo AS [Linked Client 1 A/C No],

LinkedClient2.LinkDesc+' - '+LinkedClient2.LinkedClientName+' - REL:'+LinkedClient2.LinkRel+' - Type: '+LinkedClient2.LinkType

AS [Linked Client 2],

LinkedClient2.LinkACNo AS [Linked Client 2 A/C No],

LinkedClient3.LinkDesc+' - '+LinkedClient3.LinkedClientName+' - REL:'+LinkedClient3.LinkRel+' - Type: '+LinkedClient3.LinkType

AS [Linked Client 3],

LinkedClient3.LinkACNo AS [Linked Client 3 A/C No],

LinkedClient4.LinkDesc+' - '+LinkedClient4.LinkedClientName+' - REL:'+LinkedClient4.LinkRel+' - Type: '+LinkedClient4.LinkType

AS [Linked Client 4],

LinkedClient4.LinkACNo AS [Linked Client 4 A/C No],

LinkedClient5.LinkDesc+' - '+LinkedClient5.LinkedClientName+' - REL:'+LinkedClient5.LinkRel+' - Type: '+LinkedClient5.LinkType

AS [Linked Client 5],

LinkedClient5.LinkACNo AS [Linked Client 5 A/C No],

LinkedClient6.LinkDesc+' - '+LinkedClient6.LinkedClientName+' - REL:'+LinkedClient6.LinkRel+' - Type: '+LinkedClient6.LinkType

AS [Linked Client 6],

LinkedClient6.LinkACNo AS [Linked Client 6 A/C No],

LinkedClient7.LinkDesc+' - '+LinkedClient7.LinkedClientName+' - REL:'+LinkedClient7.LinkRel+' - Type: '+LinkedClient7.LinkType

AS [Linked Client 7],

LinkedClient7.LinkACNo AS [Linked Client 7 A/C No],

LinkedClient8.LinkDesc+' - '+LinkedClient8.LinkedClientName+' - REL:'+LinkedClient8.LinkRel+' - Type: '+LinkedClient8.LinkType

AS [Linked Client 8],

LinkedClient8.LinkACNo AS [Linked Client 8 A/C No],

LinkedClient9.LinkDesc+' - '+LinkedClient9.LinkedClientName+' - REL:'+LinkedClient9.LinkRel+' - Type: '+LinkedClient9.LinkType

AS [Linked Client 9],

LinkedClient9.LinkACNo AS [Linked Client 9 A/C No],

LinkedClient10.LinkDesc+' - '+LinkedClient10.LinkedClientName+' - REL:'+LinkedClient10.LinkRel+' - Type: '+LinkedClient10.LinkType

AS [Linked Client 10],

LinkedClient10.LinkACNo AS [Linked Client 10 A/C No]

FROM ((((((((((Clients

LEFT JOIN LinkedClient1 ON Clients.ClientRef = LinkedClient1.ClientRef)

LEFT JOIN LinkedClient2 ON Clients.ClientRef = LinkedClient2.ClientRef)

LEFT JOIN LinkedClient3 ON Clients.ClientRef = LinkedClient3.ClientRef)

LEFT JOIN LinkedClient4 ON Clients.ClientRef = LinkedClient4.ClientRef)

LEFT JOIN LinkedClient5 ON Clients.ClientRef = LinkedClient5.ClientRef)

LEFT JOIN LinkedClient6 ON Clients.ClientRef = LinkedClient6.ClientRef)

LEFT JOIN LinkedClient7 ON Clients.ClientRef = LinkedClient7.ClientRef)

LEFT JOIN LinkedClient8 ON Clients.ClientRef = LinkedClient8.ClientRef)

LEFT JOIN LinkedClient9 ON Clients.ClientRef = LinkedClient9.ClientRef)

LEFT JOIN LinkedClient10 ON Clients.ClientRef = LinkedClient10.ClientRef)

) AS [SubQuery]

GROUP BY SubQuery.ClientRef, SubQuery.Account_Number, SubQuery.ClientName

, SubQuery.[Linked Client 1], SubQuery.[Linked Client 1 A/C No]

, SubQuery.[Linked Client 2], SubQuery.[Linked Client 2 A/C No]

, SubQuery.[Linked Client 3], SubQuery.[Linked Client 3 A/C No]

, SubQuery.[Linked Client 4], SubQuery.[Linked Client 4 A/C No]

, SubQuery.[Linked Client 5], SubQuery.[Linked Client 5 A/C No]

, SubQuery.[Linked Client 6], SubQuery.[Linked Client 6 A/C No]

, SubQuery.[Linked Client 7], SubQuery.[Linked Client 7 A/C No]

, SubQuery.[Linked Client 8], SubQuery.[Linked Client 8 A/C No]

, SubQuery.[Linked Client 9], SubQuery.[Linked Client 9 A/C No]

, SubQuery.[Linked Client 10], SubQuery.[Linked Client 10 A/C No]

ORDER BY SubQuery.ClientName

Regards

Steve