SQL Matching between two tables

purejamie
purejamie used Ask the Experts™
on
Hi there,

Hopefully this is another simple SQL (2008) question!

I have two tables, table_1 and table_2. These tables have a column in common, product_code. Also there is a column called price_when_sold in both tables.

I would like to find product_code's from table 1 that are also in table 2, then sum the price_when sold in table 1 for that product_code, then also sum the price_when_sold in table 2 for that product_code. Then present them as columns in the same results.

Hopefully this is simple and quick for someone! :)

Cheers,
Jamie.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
SELECT a.Product_Code, Price_When_sold1, Price_When_Sold2
FROM  (SELECT Product_Code, SUM( Price_When_sold)   Price_When_sold1
           FROM Table1 GROUP BY Product_code ) a
INNER JOIN  (SELECT Product_Code, SUM( Price_When_sold)   Price_When_sold2
           FROM Table2 GROUP BY Product_code ) b on a.Product_code = b.product_code
HainKurtSr. System Analyst

Commented:
can you please post teh a sample result... if above post is not what you want...

Author

Commented:
Hi There,

The first answer has got me pretty much where I want, I just need to refine it slightly if that is OK. Each table also has a Sale_Date column. I would like to group the results into quarters lke

Product Code          Quarter 1                             Quarter 2
                           Table 1 Table 2                  Table 1   Table 2

12343242343     $150       $130                      $123      $231

Also, could I show only when table 1 and table 2  totals match please..

Many thanks for your help so far... Promise this is the last part of the question!

Cheers,
Jamie.
HainKurtSr. System Analyst

Commented:
how come you have 4 sale for one item? you said two tables two sale columns!...
SharathData Engineer
Commented:
check this
SELECT a.Product_Code, a.Quart,Price_When_sold1, Price_When_Sold2
FROM  (SELECT Product_Code,datepart(q,Sale_Date) Quart, SUM( Price_When_sold)  Price_When_sold1 
           FROM Table1 GROUP BY Product_code,datepart(q,Sale_Date) ) a 
INNER JOIN  (SELECT Product_Code, datepart(q,Sale_Date) Quart,SUM( Price_When_sold)   Price_When_sold2
           FROM Table2 GROUP BY Product_code,datepart(q,Sale_Date) ) b 
   on a.Product_code = b.product_code and a.Quart = b.Quart
where Price_When_sold1 = Price_When_Sold2

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial