user3733648 user3733648 - 2 months ago 6
SQL Question

Filtering the rows from Query result

I am trying to get only rows that exist in both season. in both season the products have same

code

For example in season 2016 I have 315 products in
products
table and for 2017 only 28 products

The following query delivers 315 + 28 products.

SELECT *
FROM products P
INNER JOIN Season S ON S.id = P.id_season
WHERE P.active = 1
AND S.season IN ( 2016, 2017 )
ORDER BY P.code


what I need is that query should deliver only 28 products from 2017 and 28 from 2016 which have the same code as of 2017's products.

Answer

It looks like intersection:

SELECT P.*
FROM products P
INNER JOIN Season S ON S.id = P.id_season
WHERE P.active = 1
AND S.season=2016
INTERSECT
SELECT P.*
FROM products P
INNER JOIN Season S ON S.id = P.id_season
WHERE P.active = 1
AND S.season=2017
ORDER BY P.code

Or, another aproach. Find records from 2016, find from 2017 and match codes:

SELECT * FROM
(
    SELECT *
    FROM products P
    INNER JOIN Season S ON S.id = P.id_season
    WHERE P.active = 1
    AND S.season=2016
) T1
JOIN
(
    SELECT *
    FROM products P
    INNER JOIN Season S ON S.id = P.id_season
    WHERE P.active = 1
    AND S.season=2017
) T2 ON T1.Code=T2.Code
ORDER BY T1.Code