Menu
I'm trying to find a query that selects every row of an associative table where the second column indicates different values that must all be matched with the first column's.
Example: I have column X and Y. I want to get the values of X where X is defined with every Y specified.
x y
======
a 1
a 2
b 1
a 3
c 2
c 3
SELECT DISTINCT x FROM table WHERE y AND (2, 3)
This query of course isn't valid. I would expect to get a and c somehow.
As I'm also trying to learn MySQL queries better, I would appreciate if you could give an explanation of the logic behind your answer if you can provide one. Thanks! :)
774
The symbol <> in MySQL is same as not equal to operator (!=). Both gives the result in boolean or tinyint(1). If the condition becomes true, then the result will be 1 otherwise 0. Case 1 − Using !=
Example - Less Than or Equal Operator In MySQL, you can use the <= operator to test for an expression less than or equal to. SELECT * FROM inventory WHERE product_id <= 300; In this example, the SELECT statement would return all rows from the inventory table where the product_id is less than or equal to 300.
In MySQL every query generates a temporary result or relation. In order to give a name to those temporary result set, CTE is used. A CTE is defined using WITH clause. Using WITH clause we can define more than one CTEs in a single statement.
Introduction to MySQL recursive CTE A recursive CTE is a subquery that refers to itself by name. We use the WITH RECURSIVE clause to define recursive CTEs. Recursive CTE should have a terminating condition. The recursive CTEs can generate series and traverse hierarchical or tree-structured data.
I hope this is what you're looking for. If you confirm it,I'll explain you the query.
select x
from table
where y in (2,3)
group by x
having count(distinct(y)) = 2
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
