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:

Table2:

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

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!

CodePudding user response：

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.