CODEWITHSUNDEEP
sqlsql-server
user3733648
user3733648

Reputation: 1333

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.

Upvotes: 1

Views: 50

Answers (3)

Paweł Dyl
Paweł Dyl

Reputation: 9143

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

Upvotes: 3

Jones Joseph
Jones Joseph

Reputation: 4938

SELECT *
FROM products P
INNER JOIN Season S ON S.id = P.id_season
WHERE P.active = 1
AND S.season IN ( 2017 )
Union All
SELECT *
FROM products
where active = 1
AND Prod_id in (
    SELECT Prod_Id FROM products P INNER JOIN Season S ON S.id = P.id_season
    WHERE  P.active = 1 AND S.season IN ( 2017 ) 
)

Does this work ?

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

You have to change and condition:

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

Upvotes: 1

Related Questions