CODEWITHSUNDEEP
phpsqlsql-server
user3733648
user3733648

Reputation: 1333

SELECTing Products from products table

I want to select the rows from products table. The products are season based.

each product row/entity contains a column named id_season

and seasons table looks like

id | season_name | active | created | modified

Season names are Year like 2016,2017,2018 ...

I want to select all the products from 2016 and 2017 which have same code

I have a simple select like

SELECT *
FROM products P
INNER JOIN seasons S ON S.id = P.id_season
WHERE S.active = 1
AND S.season_name IN ( YEAR(GETDATE()), YEAR(GETDATE()) + 1  )

but don't know how to refine it, to match the codes on products from different seasons.

Upvotes: 0

Views: 431

Answers (2)

Raj
Raj

Reputation: 45

You can use inner query as follows:

 SELECT * FROM products
 where id_season in (Select id from seasons where
 season_name IN ( YEAR(GETDATE()), YEAR(GETDATE()) + 1  ));

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below code..

;WITH cte_1
AS
(SELECT *,COUNT(p.code) OVER(partition by p.code Order by  p.code) cnt
 FROM products P
   INNER JOIN seasons S ON S.id = P.id_season
WHERE S.active = 1
AND S.season_name IN ( YEAR(GETDATE()), YEAR(GETDATE()) + 1  )) -- or simply put IN ('2016','2017')
SELECT *
FROM cte_1
WHERE cnt>1

or you can use a subquery format as below.

SELECT *
FROM
(SELECT *,COUNT(p.code) OVER(partition by p.code Order by  p.code) cnt
 FROM products P
   INNER JOIN seasons S ON S.id = P.id_season
WHERE S.active = 1
AND S.season_name IN ( YEAR(GETDATE()), YEAR(GETDATE()) + 1  )) t
WHERE  t.nt>1

Upvotes: 1

Related Questions