September 27th, 2018, 12:15 PM
-
Filtering Results
I am currently using the IN statement to filter results from a single column but the results are not exactly what I am looking for. I understand the IN attribute is working as expected I just do not know what I should be using. I have tried everything from a simple UNION to a variety of JOIN statements. The result I would like from the example below is 4 not 3 and 4.
SELECT * FROM table WHERE column2 IN ('30','36')
column1 | column2
3 | 30
4 | 30
4 | 36
September 27th, 2018, 01:43 PM
-
Assuming what you want are results that match BOTH values, you would use IN() in the WHERE clause to determine what data is matched, but you would typically use a query with the following - SELECT your_list_of_columns_here, COUNT(*) as cnt to get a count of rows in each group, GROUP BY column1 to group by the column1 value, HAVING cnt = 2 to filter only the rows in the result set that have a count of 2, i.e. matching both of the IN(...) values.
September 27th, 2018, 02:09 PM
-
Code:
SELECT that.column1
, that.column2
FROM ( SELECT column1
FROM table
WHERE column2 IN (30,36)
GROUP
BY column1
HAVING COUNT(DISTINCT column2) = 2 ) AS this
INNER
JOIN table AS that
ON that.column1 = this.column1