How to join 5 tables in SQL and show only 2 row of data

Question

Sorry if i'm made a mistake in my thread's title.

I want to ask query about joining 5 tables and showing only 2 row in my asp.net gridview list

I have created 3 tables

A: CustId, CodeA, FullNameA, SurNameA
B: CustId, CodeB, FullNameB, SurNameB
C: CustId, CodeC, FullNameC, SurNameC

All of that tables will be joined with [Customer], and [Code] tables to show 'CustomerName' from [Customer] and 'CodeName' from [Code]

There are the data in [A],[B],[C], [Customer], And [Code] Tables:

A
-------------------------------------------------------------
CustId | CodeA  | FullNameA | SurNameA
71       A001     Test1A      Sur1A
71       A002     Test2A      Sur2A

B
-------------------------------------
CustId | CodeB  | FullNameB | SurNameB
71       B001     Test1B      Sur1B
71       B002     Test2B      Sur2B

C
-------------------------------------------
CustId | CodeC  | FullNameC | SurNameC
71       C001     Test1C      Sur1C
71       C002     Test2C      Sur2C

Customer
=============================================
CustId | CustomerName  | Gender |
71       Agripa          Male

Code
=============================================
CodeID | CodeName    | CodeName2 |
A001     Amoxiclyin    Amoxyclin
B001     Brulamin      Brulamin
C001     AXMetan       AXMetan
A002     Aloximin      Aloximin

etc until C002...

There is my query:

SELECT 
cst.CustomerName, cd1.CodeName, cd2.CodeName, cd3.CodeName
, a.*, b.*, c.*
FROM [A] a
INNER JOIN [Customer] cst ON a.CustId = cst.CustId
INNER JOIN [B] b ON b.CustId = cst.CustId
INNER JOIN [C] c ON c.CustId = cst.CustId
INNER JOIN [Code] cd1 ON cd1.CodeID = a.CodeA
INNER JOIN [Code] cd2 ON cd2.CodeID = b.CodeB
INNER JOIN [Code] cd3 ON cd3.CodeID = c.CodeC

I want to show it to list only 2 row based on A,B,C Table that only have 2 row , for example:

CustomerName | CodeNameA | FullnameA | SurNameA | CodeNameB | FullNameB | .....
Agripa         Amoxiclyn   Test1A      Sur1A       Test1B      Sur1B      .....
Agripa         Aloximin    Test2A      Sur2A       Test2B      Sur2B      .....

But, it show:

CustomerName | CodeNameA | FullnameA | SurNameA | CodeNameB | FullNameB | .....
Agripa         Amoxiclyn   Test1A      Sur1A       Test1B     Sur1B       .....
Agripa         Aloximin    Test2A      Sur2A       Test2B     Sur2B       .....
Agripa         Aloximin    Test1A      Sur1A       Test1B     Sur1B       .....
Agripa         Amoxiclyn   Test1B      Sur2A       Test2B     Sur2B       .....
Agripa         Aloximin    Test1B      Sur1B       Test2B     Sur1B       .....

...until 8 row (and its mixed up with the data) -> it should be only 2

since I only have 2 data input in A,B,C tables

Please help me to know what should i do with the query to show only 2 row based on 2 row input in A,B,C tables and not mixed up and create another necessary row. Thanks.

why 71 is in available in 3 tables A, B and C and why we need to join A, B and C, is there any significant meaning for those — Fahmi, Sep 27 '18 at 09:30
You should check this: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server it could be what you want to achieve — AlbertoCh, Sep 27 '18 at 11:24
@smoldev . . . Doesn't `select top (2) . . .` do what you want? — Gordon Linoff, Sep 27 '18 at 11:31

bii · Accepted Answer · 2018-09-27T09:37:41.067

0

You should add DISTINCT and GROUP BY and then use LEFT JOIN insted of INNER JOIN something like this:

SELECT DISTINCT cst.CustomerName, cd1.CodeName, cd2.CodeName, cd3.CodeName, a.*, b.*, c.* 
FROM [A] a 
INNER JOIN [Customer] cst 
ON a.CustId = cst.CustId 
INNER JOIN [B] b 
ON b.CustId = cst.CustId 
INNER JOIN [C] c 
ON c.CustId = cst.CustId 
INNER JOIN [Code] cd1 
ON cd1.CodeID = a.CodeA 
INNER JOIN [Code] cd2 
ON cd2.CodeID = b.CodeB 
INNER JOIN [Code] cd3 
ON cd3.CodeID = c.CodeC 
GROUP BY cst.CustomerName, cd1.CodeName, cd2.CodeName, cd3.CodeName, a.*, b.*, c.*

And you should get this result:

CUSTOMERNAME || CODENAME || CODENAME || CODENAME || CODEA || FULLNAMEA || SURNAMEA
Agripa          Aloximin    Brulamin    AXMetan      A002     Test2A      Sur2A 
Agripa         Amoxiclyin   Brulamin    AXMetan     A001      Test1A        Sur1A

edited Sep 27 '18 at 09:37

answered Sep 27 '18 at 07:49

bii

66
3

Thanks for ur answer, I did that and it still showing 8 row with mixed data instead of 2. – smoldev Sep 27 '18 at 07:57
did you used: `LEFT JOIN`? I have edited my first answer swapping last 3 `INNER JOIN` with `LEFT JOIN` – bii Sep 27 '18 at 08:05
yeah i did use left join and it still showing 8 row. – smoldev Sep 27 '18 at 08:10
My bad, I've added `LEFT JOIN` but there was no need. `INNER JOIN` is what you need. I've edited my answer accordingly – bii Sep 27 '18 at 09:39

How to join 5 tables in SQL and show only 2 row of data

since I only have 2 data input in A,B,C tables

1 Answers1