CODEWITHSUNDEEP
sqlsql-serverjoin
Riya
Riya

Reputation: 157

How to select data from 3 tables in SQL with GROUP BY?

I got confused to select exact data from three tables. The data needs to select is more than 200k records. I got

Table 1: Header 

  No.    Code
  -------------
  1      AAA
  2      BBB
  3      CCC
  4      DDD

Table 2: Detail:

  No.    I-Code
 ---------------
  1      123
  1      122
  1      111
  2      222
  2      212
  3      333
  3      341

Table 3: Inventory 

 I-code  Code
 ------------
  123     AAA
  122     ACC
  111     ACE
  222     BAA
  212     BVC
  333     DVC
  341     CVD

I want to select I-Code, Code from table 3 along with Code in table 1 group by No in table 2. Is this possible? I tried many methods, it makes me more and more confused.

One of the query that I tried is 

SELECT 
    inventory.I-code, inventory.Code,header.Code
FROM 
    inventory
INNER JOIN
    Detail ON inventory.I-code = Detail.I-code
INNER JOIN
    header ON header.No =Detail.No

It gives me the data but the No was duplicated. I tried to group the No also.

Expected Output.

No[from tbl 1]   I-code[from tbl 3]   Code[from tbl 3]{GROUPBY} Code[frm tbl 1]
   1                     123                   AAA                AAA
   1                     122                   ACC                AAA  
   1                     111                   ACE                AAA
   2                     222                   BAA                BBB
   3                     341                   CVD                CCC

Kindly help.

Thanks in advance.

Upvotes: 2

Views: 1278

Answers (2)

Asif Hakim
Asif Hakim

Reputation: 66

you can use another query to get same result

  with t as (select   H.no, H.code,d.[I-code] from 
  Header H inner join Detail D on  H.no= D.no) 

  select  t.NO as [No from tbl 1]  ,i.[I-code] as  [I-code from tbl 3],i.code as  [Code 
 from tbl 3], t.code as [Code frm tbl 1] from Inventory I inner join t on i.code=t.code

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try below using subquery and join

select x.*,y.code from 
(select a.no,a.code,b.icode from 
  table1 a inner join table2 b on a.no=b.no)x inner join table3 y on x.icode=y.code

Upvotes: 0

Related Questions