How join 3 different tables in SQL Server

Question

I have three tables in SQL Server, 2 tables save many items of product such as product number, product name and product line and another table save stop time of machine.

I have written this code but I cant join 2 queries into one:)

(select  
     Query4.prdline, Query4.prdproduction, Query4.SumOfProduced,  
     (Query3.SumOfTime) 
 from 
     (select 
          Table_production.prdline, Table_production.prdproduction, 
          sum(Table_stop.stoptime) AS SumOfTime
      from
          Table_production 
      inner join
          Table_stop ON Table_production.prdID = Table_stop.formnum
      where
          (((Table_production.[prddate]) Between '1397/08/01' And '1397/08/30'))
      group by
          Table_production.prdline, Table_production.prdproduction) Query3,
(select
     (t.prdline), (t.prdproduction), sum(t.prdnum) AS SumOfProduced
 from
     (select
          prdnum, prdtime, prdproduction, prdline 
      from
          Table_production 
      where 
          (prddate between '1397/08/01' and '1397/08/30')

      union all

      select
          nprdnum, nprdtime, nprdproduction, nprdline 
      from
          Table_Nextproduction 
      where 
          (nprddate between '1397/08/01' and '1397/08/30')
     ) T   
 group by
     T.prdline, T.prdproduction) Query4)

Possible duplicate of [SQL Inner-join with 3 tables?](https://stackoverflow.com/questions/10195451/sql-inner-join-with-3-tables) — Rafael, Nov 05 '18 at 00:23

score 0 · Answer 1 · answered Nov 04 '18 at 12:47

Add in the JOIN syntax:

select  
 Query4.prdline, Query4.prdproduction, Query4.SumOfProduced,  
 (Query3.SumOfTime) 
 from 
 (select 
      Table_production.prdline, Table_production.prdproduction, 
      sum(Table_stop.stoptime) AS SumOfTime
  from
      Table_production 
  inner join
      Table_stop ON Table_production.prdID = Table_stop.formnum
  where
      (((Table_production.[prddate]) Between '1397/08/01' And '1397/08/30'))
  group by
      Table_production.prdline, Table_production.prdproduction) Query3
join
(select
 (t.prdline), (t.prdproduction), sum(t.prdnum) AS SumOfProduced
 from
 (select
      prdnum, prdtime, prdproduction, prdline 
  from
      Table_production 
  where 
      (prddate between '1397/08/01' and '1397/08/30')

  union all

  select
      nprdnum, nprdtime, nprdproduction, nprdline 
  from
      Table_Nextproduction 
  where 
      (nprddate between '1397/08/01' and '1397/08/30')
 ) T   
 group by
 T.prdline, T.prdproduction) Query4)
on Query3.prdline = Query4.prdline
and Query3.prdproduction = Query4.prdproduction;

score 0 · Accepted Answer · answered Nov 04 '18 at 14:17

You can use an Inner Join to do the needful. Please refer the below-modified query(Code comments added) by adding 'Inner Join' and the relevant table joinings.

select  
 Query4.prdline, Query4.prdproduction, Query4.SumOfProduced,  
 (Query3.SumOfTime) 
 from 
 (select 
      Table_production.prdline, Table_production.prdproduction, 
      sum(Table_stop.stoptime) AS SumOfTime
  from
      Table_production 
  inner join
      Table_stop ON Table_production.prdID = Table_stop.formnum
  where
      (((Table_production.[prddate]) Between '1397/08/01' And '1397/08/30'))
  group by
      Table_production.prdline, Table_production.prdproduction) Query3
inner join --INNER JOIN added
(select
 (t.prdline), (t.prdproduction), sum(t.prdnum) AS SumOfProduced
 from
 (select
      prdnum, prdtime, prdproduction, prdline 
  from
      Table_production 
  where 
      (prddate between '1397/08/01' and '1397/08/30')

  union all

  select
      nprdnum, nprdtime, nprdproduction, nprdline 
  from
      Table_Nextproduction 
  where 
      (nprddate between '1397/08/01' and '1397/08/30')
 ) T   
 group by
 T.prdline, T.prdproduction) Query4)
on Query3.prdline = Query4.prdline  -- JOIN Query3 and Query 4 prdline
and Query3.prdproduction = Query4.prdproduction;   -- JOIN Query3 and Query 4 prdproduction

thanks prasan for answer my question but my union all don't work — Mehrdad, Nov 05 '18 at 04:14

How join 3 different tables in SQL Server

2 Answers2