SQL query where 'IN' act as 'AND' not 'OR'?

Question

Pardon the title, I've no clue what to call this. So imagine I have this

table_ref

id_x|id_y
---------
6|70
6|71
6|72
6|73
8|70
8|73
9|72
9|73

How can i select id_y only if it matches id_x= 6 & 8 & 9? in this case it should return me 73

the returned result of id_y will then be used as an inner join in another sql query.

Borehole

how come id_x is 6, 8, and 9 as well at the same time?? id_x only has one value at a time, right? so it will be 6, 8, OR 9.. – Leathern

it's a fk of table_x pk and id_y is a fk to table_y pk – Borehole

you should use left join 3 times – Litigation

This is called relational division. – Hawsepipe

possible duplicate of SQL: Return "true" if list of records exists? – Equanimous

Answer 1

Of course, it is hard to parametrize, but if it important then you can pass the values as table-valued parameter.

SELECT T.id_y
FROM table_ref T
  JOIN (VALUES (6), (8), (9)) A(id_x)
    ON T.id_x = A.id_x
GROUP BY T.id_y
HAVING COUNT(*) = 3

Spermatogonium

@hamlet, is there any tutorials available for what concept you have used? I know TVP(table values parameters) basics. – Litigation

@AK47, Table Value Constructor (Transact-SQL) – Spermatogonium

@Litigation maybe you also want to look at this Relational Division in SQL – Glairy

This assumes that id_x and id_y combinations are unique in table_ref. It could be strengthened by using Count(DISTINCT [T].[id_x]) = 3 – Spiculate

@Jodrell, This not better, just yours is wrong. You must swap last two lines. The duplicate rows is option for OP. But in case, if the list is bigger, using JOIN in conjunction with virtual table is preferred than IN clause. – Spermatogonium

@JohnKiernander, Probably why Codd described the relational model using sets and not the bags that SQL uses. – Debauched

@HamletHakobyan how do you accommodate this code if there are duplications of id_x & id_y? Is it good enough if I just subquery it to get distinctive data and then do the 'having'? – Borehole

@sharify, it is depends how do you get the source data. In general, using the COUNT(DISTINCT id_x) = 3 is an option. If you want id_y values that matches for all id_x, the @Serpiton answer is doing trick. Anyway, I assume that the collection of id_x to match, you must pass as a table-valued parameter, when the distinct count of items is known in advance. – Spermatogonium

Answer 2

SELECT distinct [idy] 
FROM Table_1
WHERE idy in (SELECT idy FROM Table_1 WHERE idx=6)
AND idy in (SELECT idy FROM Table_1 WHERE idx=8)
AND idy in (SELECT idy FROM Table_1 WHERE idx=9)

Hanlon

Answer 3

Please try this:

select a.* from  table_ref as a
inner join table_ref as b 
        on a.id_y = b.id_y
        and b.id_x = 8
inner join table_ref as c
        on a.id_y = c.id_y
        and c.id_x = 9
where a.id_x = 6

Litigation

Answer 4

Yes you can use a plain IN to accomplish that:

sqlite> SELECT * FROM table_ref;
6|70
6|71
6|72
6|73
8|70
8|73
9|72
9|73
sqlite> SELECT id_y
...> FROM table_ref t
...> WHERE id_x IN (6,8,9)
...> GROUP BY t.id_y
...> HAVING COUNT(*) = 3;
73          

Debauched

What if there is 3 times 6|70? It will be selected but that is not required. – Lorraine

@Lorraine Good point. This table looked like all key to me, and I expect it to have a uniqueness constraint on it... From the relational model: there is a proposition P(id_x, id_y) with this relation table_ref, such that for each tuple t(id_x, id_y) in table_ref P(id_x, id_y) is True (under the closed world assumption). Repeating tuples doesn't make P any True-er. Which is why the Relational Model was defined with sets and not that bag mess that SQL made of it. – Debauched

yeah actually my table has many occurrence of the same id_x and/or id_y, if I use this where there is 4 times of 6|70 it won't gives the correct result, subquery did the job for me. – Borehole

Answer 5

This works even on SQL-Server 2005.

You can use multiple EXISTs:

SELECT DISTINCT  t.id_y
FROM dbo.table_ref t
WHERE EXISTS(
   SELECT 1 FROM dbo.table_ref t2
   WHERE t2.id_y=t.id_y AND t2.id_x = 6
)
AND EXISTS(
   SELECT 1 FROM dbo.table_ref t2
   WHERE t2.id_y=t.id_y AND t2.id_x = 8
)
AND EXISTS(
   SELECT 1 FROM dbo.table_ref t2
   WHERE t2.id_y=t.id_y AND t2.id_x = 9
)

Demo

That doesn't look nice but it's working and efficient with proper indices.

Indentation

yeah, I had similar query with many subqueries, trying to avoid it, as the id_x can be a list of 10-20, this looks good though. – Borehole

@sharify: if it's a list of only 10-20 i would still use this approach since it's sargable, which meens that it can be optimized by the db. It's quite simple since you only have to copy-paste the EXISTs and replace the value. What version of MS SQL-Server are you using? – Indentation

@sharify: i've overlooked that you are using SQL-Server 2008. Then the accepted answer is much more concise of course. – Indentation

Answer 6

using inner join :

SELECT a.id_y
FROM
  (SELECT id_x, id_y 
   FROM table_ref 
   WHERE id_x =6 )a INNER JOIN
  (SELECT  id_x, id_y 
   FROM table_ref 
   WHERE id_x =8)b on a.id_y = b.id_y
 INNER JOIN
 (SELECT id_x, id_y 
  FROM table_ref  
  WHERE id_x =9)c on b.id_y = c.id_y

demo here:http://sqlfiddle.com/#!3/28bcb/6

Adalbert

Answer 7

This should work:

SELECT          [id_y]
FROM            [table_ref]
WHERE           [id_x] IN (6, 8, 9)
GROUP BY        [id_y]
HAVING          Count(DISTINCT [id_x]) = 3

If you want to get the range of values from a separate table you can do that with:

DECLARE @Lookup TABLE (ID INT)
INSERT INTO @Lookup VALUES (6), (8), (9)

SELECT          [id_y]
FROM            [table_ref] 
WHERE           [id_x] IN (SELECT [ID] FROM @Lookup)
GROUP BY        [id_y]
HAVING          Count(DISTINCT [id_x]) = (SELECT Count(DISTINCT [ID]) FROM @Lookup)

Spiculate

Answer 8

If you want something a little more general:

SELECT   id_y
FROM     table_ref
GROUP BY id_y
WHERE    COUNT(DISTINCT id_x)) = (SELECT COUNT(DISTINCT id_x)) FROM table_ref)

It return only the value of id_y associated with every id_x in the table, regardless of the number of id_x

Unnumbered

Answer 9

How about this?

Select blah
From OtherTable
Where id_y in ( Select id_y from table_ref where id_x in (6,8,9))

Suspension

this is no different than just using IN, which gives me all the y values – Borehole

I'm not entirely sure what you want to achieve. In your post you wanted to select a number of id_y values when specifying id_x values and use that as the basis of another query. I have demonstrated that here. If there is something else you need, feel free to ask! – Suspension

OP's question was a bit confusingly wording, but as seen in the other answers, he was looking for all the id_ys that appear alongside all of 6, 8, and 9 in the id_x column. In SQL terms, this — https://mcmap.net/q/525599/-sql-query-where-39-in-39-act-as-39-and-39-not-39-or-39 — but as efficient as possible. – Endlong

Having just re-read it for the fourth time, I see what you are saying. – Suspension