CODEWITHSUNDEEP
mysqlsql
Killua87
Killua87

Reputation: 61

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

Upvotes: 1

Views: 270

Answers (4)

FanoFN
FanoFN

Reputation: 7114

You don't have to join User table twice and please use the more recommended JOIN operator over comma-join.

A comment from @stickybit made me realize that I misunderstand the question. Therefore I'll update my answer to something workable for the question requirement. I'll retain most of my original answer with little modifications just to make sure that it can return the desired result for the current set of OP data. Here's the query:


SELECT * FROM
(SELECT url,
       GROUP_CONCAT(demo) dd
  FROM Visit V 
   JOIN User U
    ON V.uid = U.uid
GROUP BY url) A
WHERE dd LIKE '%child%18-25%' OR dd LIKE '%18-25%child%';

I know this is not the best solution but I've seen others have posted their version of query based on the same understanding. So, this is just another variant.

Check the updated demo fiddle

Upvotes: 0

kmoser
kmoser

Reputation: 9273

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

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

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

Upvotes: 1

alnajm
alnajm

Reputation: 385

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

Upvotes: 1

Related Questions