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.

7
  • If id is the primary key then WHERE (id=1) AND (id = 2) AND (id = 3) will return 0 rows. Always. Commented Feb 17, 2015 at 16:17
  • 2
    If id is not the primary or unique key, then you probably want relational division. Which is the case? Commented Feb 17, 2015 at 16:17
  • 1
    Even if id is not unique, it can only be 1 OR 2 OR 3 on any single row. Commented Feb 17, 2015 at 16:47
  • 1
    I guess now we can close it as duplicate of: Search within same ID accross several rows Commented Feb 18, 2015 at 15:00
  • 1
    Or maybe this one, which has more ways to do it and a title that resembles the title here: Make IN clause behave like AND clause Commented Feb 18, 2015 at 15:06

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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