-2

I have 3 tables:

Clients table:

ID      Name
1       ClientName1
2       ClientName2
3       ClientName3

Each client may have some identification documents

Table Documents:

ID      ClientId           Document
1       1                  ClientDocument11
2       1                  ClientDocument21
3       1                  ClientDocument31

4       2                  ClientDocument12
5       2                  ClientDocument22

6       3                  ClientDocument13

Also each client may have some bank accounts

Table Accounts:

ID      ClientId           Account
1       1                  110000000000

2       2                  120000000000
3       2                  220000000000

4       3                  130000000000
5       3                  230000000000
6       3                  330000000000

I want NOT JOIN but glue or sew table Documents to table Clients and then add table Account to get a table like this :

ID      Name                Document             Account
1       ClientName1         ClientDocument11     110000000000
1       ClientName1         ClientDocument21     110000000000 
1       ClientName1         ClientDocument31     110000000000

2       ClientName2         ClientDocument12     120000000000
2       ClientName2         ClientDocument22     220000000000

3       ClientName3         ClientDocument13     130000000000
3       ClientName3         ClientDocument13     230000000000
3       ClientName3         ClientDocument13     330000000000

OR

ID      Name                Document             Account
1       ClientName1         ClientDocument11     110000000000
1                           ClientDocument21      
1                           ClientDocument31     

2       ClientName2         ClientDocument12     120000000000
2                           ClientDocument22     220000000000

3       ClientName3         ClientDocument13     130000000000
3                                                230000000000
3                                                330000000000

If you look at rows with Client2 you understand this cannot achive with JOIN:

2       ClientName2         ClientDocument12     120000000000
2       ClientName2         ClientDocument22     220000000000

Whether it is possible in SQL Server?

okpast
  • 65
  • 4
  • Without join its not possible – Ankit Bajpai Sep 17 '15 at 08:23
  • Do you want to alter Account table and insert records or just a result set? – Kaf Sep 17 '15 at 08:37
  • 1
    What you want is called a JOIN, whether you like it or not. How you display it is up to the UI. – Remus Rusanu Sep 17 '15 at 08:37
  • 1
    Not you cannot get a table like this with JOIN Look at rows with Client2. – okpast Sep 17 '15 at 08:42
  • In the expected result, is there any link between the *Document* column and the *Account* column, i.e. is there any reason ClientDocument22 and 220000000000 are on the same line? Or do you just list the client's documents in one column and the accounts in the other column? – Codo Sep 17 '15 at 08:50
  • Not - the Document column and the Account column does not have any link. Yes - just list the client's documents in one column and the accounts in the other column – okpast Sep 17 '15 at 08:55
  • https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx – A_Sk Sep 17 '15 at 09:03
  • 1
    You better leave that to the presentation layer. It's going to be simpler and perform better then in sql. – Zohar Peled Sep 17 '15 at 09:05
  • I don't know why you want to have such sql query that does not include ClientDocument12 - 220000000000, ClientDocument22 - 120000000000. Quite strange. – daniel Sep 17 '15 at 10:06

1 Answers1

2

It can be done with SQL but it's a bit a pain. The main idea is to assign line numbers to the documents and accounts (starting at 1 for each client) and then to do a full outer join by connecting the client ID and the line number:

select c.Id, c.Name, t.Document, t.Account
from Clients c
join (
  select coalesce(d.ClientId, a.ClientId) as ClientId,
    coalesce(d.Line, a.Line) as Line,
    d.Document, a.Account
  from (
    select ClientId, Document,
      row_number() over (partition by ClientId order by Document) as Line
    from Documents
  ) d
  full outer join (
    select ClientId, Account,
      row_number() over (partition by ClientId order by Account) as Line
    from Accounts
  ) a on d.ClientId = a.ClientId and d.Line = a.Line
) t on c.Id = t.ClientId
order by c.Id, t.Line;

See the fiddle.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • `FULL JOIN` works fine, but if you have more than 2-3 tables to join in such manner it quickly becomes ugly and slow. You may find my answer to a similar question useful: http://stackoverflow.com/questions/27515577/best-way-to-glue-columns-together/27523439#27523439 – Vladimir Baranov Sep 17 '15 at 11:15
  • Thanks , Codo and Vladimir Baranov ! – okpast Sep 18 '15 at 05:31