Simple joining two tables with group by-CodePudding

I have 2 tables (pics 1,2). There are 2 columns in tab1 and many in tab2. There is ID column which is same for every record in both tables. I need output (pic 3) with every column from both tables grouped by ID. Left or right join doesn't really matter. I just need match records by ID and have every column of both tables listed.

Thank you.

1 https://i.stack.imgur.com/1OmDT.png

2 https://i.stack.imgur.com/nShTy.png

3 https://i.stack.imgur.com/vLIFm.png

CodePudding user response：

You can simply use this simple query :

select a.ID, b.name, b.code, a.quantity 
from Tab1 a 
inner join Tab2 b on a.ID = b.ID 
group by a.ID, b.name, b.code, a.quantity 
order by a.ID

and you can learn more from here https://www.w3schools.com/sql/sql_join.asp

CodePudding user response：

select tab1.id,name,code, quantity from tab1 join tab2 using(id);

CodePudding user response：

The JOINs(INNER OR LEFT) will change in the below query depending on whether you need to show the ID info irrespective you have any quantity(NULL or 0 using left join) for it in tab2 or only show the quantity for matching IDs(INNER join) there.

SELECT 
  t1.*,
  SUM(t2.quantity) AS quantity
FROM
  tab1 AS t1
  INNER JOIN tab2 AS t2 ON t1.id = t2.id   -- CHANGE THIS TO LEFT JOIN IF NEED
GROUP BY 
  t1.id,
  t1.name,
  t1.code