Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 561
sharif y Avatar asked Apr 01 '14 08:04

sharif y


People also ask

Can we use not in WHERE clause in SQL?

The NOT IN operator can be used anywhere any other operator is used including WHERE clauses, HAVING clauses, IF statements, or join predicates – although they should be extremely rare in join predicates (SQL JOINS - SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN).

Can I use two and in WHERE clause in SQL?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.

What is and or not in SQL?

SQL AND, OR and NOT Operators The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.

What is <> in SQL Where?

The SQL WHERE Clause The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.


2 Answers

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 
like image 153
Hamlet Hakobyan Avatar answered Feb 01 '23 14:02

Hamlet Hakobyan


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) 
like image 42
William Calvin Avatar answered Feb 01 '23 13:02

William Calvin