CODEWITHSUNDEEP
mysqlsqlselect
HTMHell
HTMHell

Reputation: 6006

SQL Condition With Multiple Rows

I have a table which shows me my users' downloads reports. The table looks like this:

╔═══╦════════════╦═════════════╗
║   ║ url        ║ user        ║
╠═══╬════════════╬═════════════╣
║ 1 ║ Bla        ║ 1           ║
║ 2 ║ Bla Bla    ║ 1           ║
║ 3 ║ Bla Bla Bla║ 1           ║
║ 4 ║ Bla2       ║ 2           ║
╚═══╩════════════╩═════════════╝

If I want to select the user that downloaded the url Bla, I just do:

SELECT `user` FROM `links` WHERE `url` = 'Bla'

But I want to select the user that downloaded Bla and downloaded Bla Bla too.

How can I do that?

Thank you, and sorry for my English.

Upvotes: 0

Views: 120

Answers (4)

Teja
Teja

Reputation: 13534

For non-MYSQL

SELECT DISTINCT `user` FROM `links` WHERE `url`='Bla'
INTERSECT
SELECT DISTINCT `user` FROM `links` WHERE `url`='Bla Bla';

OR

For MYSQL

SELECT DISTINCT a.x AS Users_Bla_BlaBla
FROM 
( SELECT DISTINCT `user` AS x FROM `links` WHERE `url`='Bla' ) a 
INNER JOIN 
( SELECT DISTINCT `user` AS Y FROM `links` WHERE `url`='Bla Bla' ) b 
USING (x,y);

Upvotes: -1

MrSimpleMind
MrSimpleMind

Reputation: 8597

The sqlfiddle

select 
  user, 
  group_concat(url) as urls 
from 
  links
where 
  url in ('Bla', 'Bla Bla') -- add your urls here
group by user
having count(distinct url) > 1;

enter image description here

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use a self join 

select u.user
from links u
join links u1 on(u.`user`=u1.user)
where u1.url ='Bla'
and u.url= 'Bla Bla'

Fiddle

Upvotes: 1

Taryn
Taryn

Reputation: 247700

You can use a WHERE clause with a combination of GROUP BY and HAVING to get the result:

select user
from yourtable
where url in ('Bla', 'Bla Bla')
group by user
having count(distinct url) = 2;

See SQL Fiddle with Demo

Upvotes: 5

Related Questions