Easy SQL query for this?

Forum Moderators: open

Message Too Old, No Replies

Easy SQL query for this?

rankaboodle

5:23 am on Dec 19, 2007 (gmt 0)

I have a MYSQL table with the following structure:

Field - Type
-----------------
ip - varchar(15)
source - varchar(255)
timestamp - datetime
category - varchar(255)

With no primary key:

For category there are 4 possible values: "cat" "dog" "mice" "fish"

I want to do a SQL query to find all distinct ip addresses that would have each category initialized.

For example I want to find a unique ip address that would have category="cat" AND category="dog" AND category="mice" AND category="fish" in the database.

How do I do this with SQL?

Thanks!

syber

4:38 am on Dec 20, 2007 (gmt 0)

If I understand correctly, you want only those IP's that have all four categories represented. If that is the case, then:


SELECT DISTINCT ip 
from mytable as t1 
WHERE category = 'cat'  
 AND EXISTS (SELECT * FROM mytable WHERE ip = t1.ip AND category = 'dog')  
 AND EXISTS (SELECT * FROM mytable WHERE ip = t1.ip AND category = 'mice') 
 AND EXISTS (SELECT * FROM mytable WHERE ip = t1.ip AND category = 'fish')

ZydoSEO

11:58 pm on Dec 20, 2007 (gmt 0)

I generally handle these types of queries with self joins... The following should also work for the task you've described:

select distinct(m1.ip)
from mytable as m1
join mytable as m2 on m1.ip = m2.ip
join mytable as m3 on m1.ip = m3.ip
join mytable as m4 on m1.ip = m4.ip
where m1.category = 'cat' and m2.category='dog' and m3.category='mice' and m4.category='fish'

I created a test table in SQL*Server with lots of variations of data and seem to get the expected results.

[edited by: ZydoSEO at 12:10 am (utc) on Dec. 21, 2007]