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

Visit

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

CodePudding user response：

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')

CodePudding user response：

It looks like you can JOIN on the User table twice: once to find a user that is "18-25", and another to find a user that is a "child". If you find both of those (as determined by the WHERE clause) then the Visit record will be included in the results:

SELECT
  DISTINCT(V.url)
  FROM
    Visit V
  LEFT JOIN User U ON (V.uid = U.uid AND U.demo = '18-25')
  LEFT JOIN User U2 on (V.uid = U2.uid AND U2.demo = 'child')
  WHERE
    U.uid IS NOT NULL AND U2.uid IS NOT NULL

CodePudding user response：

You don't have to join User table twice and please use the more recommended JOIN operator over comma-join. To get the result you want, you can either use OR on demo or since the data is specific, you can try IN(). Consider these two query example:

/*Query 1*/

SELECT DISTINCT V.url
  FROM Visit V 
   JOIN User U
    ON V.uid = U.uid 
WHERE (U.demo = 'child' OR U.demo = '18-25');

/*Query 2*/

SELECT DISTINCT V.url
  FROM Visit V 
   JOIN User U
    ON V.uid = U.uid 
WHERE U.demo IN ('child','18-25');

Check the demo fiddle

CodePudding user response：

One way is to join the users, GROUP BY the URL, sum the occurrences of children and 18 to 25 year olds and check that these sums each exceed 0 in a HAVING clause.

SELECT v.url
       FROM Visit v
            INNER JOIN User u
                       ON v.uid = u.uid
       GROUP BY v.url
       HAVING sum(CASE
                    WHEN u.demo = 'child' THEN
                      1
                    ELSE
                      0
                  END) > 0
              AND sum(CASE
                        WHEN u.demo = '18-25' THEN
                          1
                        ELSE
                          0
                      END) > 0;

(Note: In MySQL you don't need the CASE expressions but could directly use the Boolean = expressions. But a CASE doesn't harm there either and with a CASE it'll also work in other DBMS. And since it's not entirely clear which DBMS you use a CASE expression is a safer bet.)

Another approach is to use a conjunction of EXISTS' and correlated subqueries that join the users to the visits and picks the record with the URL and demo in question. It would help if you already had a table with only the URLs. I'll simulate that with a derived table aliased x.

SELECT x.url
       FROM (SELECT DISTINCT
                    v.url
                    FROM Visit v) x
            WHERE EXISTS (SELECT *
                                 FROM Visit v
                                      INNER JOIN User u
                                                 ON u.uid = v.uid
                                 WHERE v.url = x.url
                                       AND u.demo = 'child')
                  AND EXISTS (SELECT *
                                     FROM Visit v
                                          INNER JOIN User u
                                                     ON u.uid = v.uid
                                     WHERE v.url = x.url
                                           AND u.demo = '18-25');