userid pageid
123 100
123 101
456 101
567 100
I want to return all the userid with pageid 100 AND 101, should give me 123 only. This is probably super easy but I can't figure it out! I tried:
SELECT userid
FROM table_name
WHERE pageid=100 AND pageid=101
But it gives me 0 results. Any help please
No record has 2 values of pageid. You probably want:
SELECT userid
FROM table_name
WHERE pageid=100 OR pageid=101
In a cleaner version, you can use:
SELECT userid
FROM table_name
WHERE pageid IN (100,101)
UPDATE: Based on the question edit, Here is the answer:
SELECT userid
FROM table_name
WHERE pageid IN (100,101)
GROUP BY userid
HAVING COUNT(DISTINCT pageid) = 2
Explanation:
First of all, the WHERE clause filter out all data except pageid not equal to 101 or 102. Then, By grouping userid, we have a list of unique userid having DISTINCT pageid = 2, which means contain ONLY 1 pageid = 101 and 1 pageid = 102
Assuming a given userid can't have the same pageid more than once:
SELECT userid
FROM table_name
WHERE pageid IN (100, 101)
GROUP
BY userid
HAVING Count(*) = 2 /* number of items to match */
DISTINCT in COUNT.
SELECT userid
FROM Table1
WHERE pageid IN (100,101)
GROUP BY userid HAVING COUNT(distinct pageid) = 2;
According to your last edit you want to find all (distinct) userid where a pageid of 101 and also 101 exists. You can use EXISTS:
SELECT DISTINCT userid
FROM TableName t1
WHERE t1.userid=123
AND EXISTS(
SELECT 1 FROM TableName t2
WHERE t2.userid=t1.userid AND pageid = 100
)
AND EXISTS(
SELECT 1 FROM TableName t2
WHERE t2.userid=t1.userid AND pageid = 101
)
I assume you are confusing AND with OR, this returns multiple records:
SELECT DISTINCT userid
FROM TableName
WHERE pageid=100 OR pageid=101
USERID
123
567
Note that i have used DISTINCT to remove duplicates, since you don't want them.
You could use or instead of and
Select userid
from table
where pageid=100 or pageid=101
If you only want to get distinct results you can use the distinct keyword. For example
Select distinct userid
from table
where pageid=100 or pageid=101