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.
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).
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.
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.
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.
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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With