Joining 2 Tables and selecting duplicate entries based on several columns

Question

I've got joined two tables and now I want to show all duplicate entries (and not the single on, but both) based on criterias in both tables.

Table1:

Material_ID	Plant	Storage_old	Stock
1234	1	GH65	5
1234	1	ZG43	10
5436	1	GH65	65

Table2:

Plant	Storage_old	Storage_new
1	GH65	ZT65
1	ZG43	ZT65
1	GH65	OE86

And I want the result of the select to be like this

Material_ID	Plant	Storage_old	Storage_new	Stock
1234	1	GH65	ZT65	5
1234	1	ZG43	ZT65	10

I tried to use the select

    select
       t1.material_id,
       t1.plant_id   ,
       t1.storage_old,
       t2.storage_new,
       t1.stock
from
       t1
left join
       t2
on
       t1.plant        = t2.plant
and     t1.storage_old = t2.storage_old
group by
       t1.material_id,
       t1.plant_id   ,
       t2.storage_new
having
       count(*) > 1

with no success. How do I use the group by without selecting all columns? Thanks a lot!

Answer 1

Consider:

Query1:

SELECT t1.plant, t2.storage_new, t1.material_id
FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON (t1.plant = t2.plant) AND (t1.storage_old = t2.storage_old)
GROUP BY t1.plant, t2.storage_new, t1.material_id
HAVING (((Count(*))>1));

Query2:

SELECT Table1.Material_ID, Table1.Plant, Table1.Storage_old, Query1.storage_new, Table1.Stock
FROM Table1 INNER JOIN Query1 ON Table1.Material_ID = Query1.material_id;

Can nest SQL of Query1 within Query2 for an all-in-one statement.