What is the correct SQL for using where ID in List and the items are with AND operator

Question

I need a SQL Statement to use IN operator within the items with "And" connected.

SELECT * from tbl_Users
WHERE id in [1,2,3]

is equivalent to:

SELECT * from tbl_Users
WHERE (id=1) or (id = 2) or (id = 3)

What I need is to generate using IN operator (or equivalent):

SELECT * from tbl_Users
WHERE (id=1) AND (id = 2) AND (id = 3)

Because I collect all the IDs (ex: [1,2,3,4,5]) in one string and search with it in the DB.

More Explanation:

ID is always a primary key

The case is I have a table which has all countries, and each country has several electrical standards in separate table.

Example: Germany requires always "standard1" and "standard2" USA required always "Standard1" and "standard3" France required always "Standard2"

If the user in C# application search for a country which has "Standard2", the system should give him Germany and France.

BUT If the user wants countries with "Standard1" AND "standard2", the system should only give him Germany. -> because of "AND"

My Query was

Select  * from [tables and joins]
where STANDARD_id IN [1,2]

[1,2] is a concatenated list which the user already choose from a list of standards.

score 2 · Accepted Answer · answered Feb 18 '15 at 15:11

I think what you are trying to do is relational division. Here is the best explanation I could find

https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

Honestly I do not fully understand this myself yet, but I believe this should work (using an intermediate table):

CREATE TABLE tblCountry (
    countryId int,
    countryName nvarchar(100)
)

INSERT INTO tblCountry
    SELECT 1, 'Germany' UNION
    SELECT 2, 'United States' UNION
    SELECT 3, 'France'

CREATE TABLE tblStandards (
    standardId int,
    standardName nvarchar(100)
)

INSERT INTO tblStandards
    SELECT 1, 'standard_1' UNION
    SELECT 2, 'standard_2' UNION
    SELECT 3, 'standard_3'

CREATE TABLE tblCountryStandards (
    countryId int,
    standardId int
)

INSERT INTO tblCountryStandards
    SELECT 1, 1 UNION
    SELECT 1, 2 UNION
    SELECT 2, 1 UNION
    SELECT 2, 3 UNION
    SELECT 3, 2

SELECT countryName
FROM tblCountry
    INNER JOIN tblCountryStandards ON tblCountry.countryId = tblCountryStandards.countryId
    INNER JOIN tblStandards ON tblStandards.standardId = tblCountryStandards.standardId
WHERE tblStandards.standardId IN (1, 2) -- unique standards selected by user
GROUP BY tblCountry.countryName
HAVING COUNT(DISTINCT tblStandards.standardId) = 2 -- number of standards selected by user above

This is based on your example, but I tested more combinations and it works if the commented values are changed.

There are many other ways of doing the division explained in the link provided above - you should use the one that suits your purposes best.

What is the correct SQL for using where ID in List and the items are with AND operator

1 Answers1