0

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.