How to make a query that filter out two AND conditions on same column

Write a query that returns all pages that have been visited by at least one child (demo=’child’) and ALSO has been visited by at least one person aged 18-25 (demo=’18-25′). Your query should return a set of urls.

I am not sure how to write a query that filters out results based on Two AND statements on the same column and it returns out an empty set.

These are the two tables:

User

uid	ip	demo
A	001	child
B	002	18-25

Visit

url	dt	uid	src	rev
A01	1890-05-14	A	A02	10
A01	002	B	A03	15

Select distinct V.url
from Visit V, [User] Z, [User] F
WHERE V.uid = Z.uid AND V.uid = F.uid
AND Z.demo = 'child' AND F.demo = '18-25'

This above code returns me an empty set.

I want it to return A01 as the url

Answer

First, you don’t need to use User table twice in the select expression.

I think it can be solved by using nested queries or as called subquery.

In explanation: first you will query the items match the first condition (demo=’child’) then you will search on the results for the items that also match the second condition (demo=’18-25′).

Your code will be like this:

Select distinct V.url
from Visit V, [User] Z
WHERE V.uid = Z.uid AND Z.demo = 'child'
AND V.url IN (Select distinct V1.url
from Visit V1, [User] Z1
WHERE V1.uid = Z1.uid AND Z1.demo = '18-25')