CODEWITHSUNDEEP
sql-server
Makaziwe
Makaziwe

Reputation: 11

Retrieve all the columns in a table if column for where clause has multiple combinations

I have a table with data that i need to retrieve for each id and its corresponding month every id has a specific month in which i need to use as a condition to select the records from my table. I would like to know if there is another way of me achieving this instead of having where in () with all the values , my sample is just a small snippet of the actuality data size

enter image description here

Is there any other way I can do this? please may you reopen the question, i have edited it

Upvotes: 0

Views: 62

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

You dont have to do one by one. You can use IN operator with the list of ids. 

SELECT * FROM YourTable WHERE ID IN (1,2,3....)

If you need to compare more than one column, then define table for combination.

;WITH CTE_IDMonthCombination AS
(
SELECT *
FROM 
(
VALUES
(100,1),
(101,2)
.
.
) AS t(ID, Month)
)
SELECT * FROM YourTable AS u
INNER JOIN CTE_IDMonthCombination AS c
ON c.ID = u.ID AND c.Month = u.Month;
GO

Or you can use TempTable, as you have mentioned in the comments.

SELECT *
INTO #t
FROM 
(
VALUES
(100,1),
(101,2)
.
.
) AS t(ID, Month);
GO

SELECT * FROM YourTable AS u
INNER JOIN #t AS c
ON c.ID = u.ID AND c.Month = u.Month;
GO

Upvotes: 1

Related Questions