Hi Mr. David,
I hope a bit of explanation for Steve Mayers' solution would help you.
The query by Mayers
select distinct a.username, a.ip from users a, users b where a.ip=b.ip &&
a.username != b.username;
I think I can explain the behaviour of the query:
Suppose the table contains the data:
username ip
a 1
b 2
c 1
Considert the query without the where condition ie
Select a.usernam, a.ip from users a, users b
This is a self join cross product. A self join without a where condition would
give the so called cross product.
ie for each row in first table all the rows of the seccond table (3*3 = 9
rows).
For clarity's sake we change the query to include the cols of the second
table b
ie issue the query Select a.*, b.* from users a, users b
a b
user ip user ip
a 1 a 1
b 2 a 1
c 1 a 1
a 1 b 2
b 2 b 2
c 1 b 2
a 1 c 1
b 2 c 1
c 1 c 1
Now consider the first where condition ie a.ip = b.ip. This tells the database
to include only those rows for which the ip col of table a is
the same as that of b. The filtered output due to this clause is
a b
user ip user ip
a 1 a 1
c 1 a 1
b 2 b 2
a 1 c 1
c 1 c 1
Add the second clause also to the query. ie the where condition becomes
"a.ip = b.ip and a.username != b.username ":
Now the rows from the above output where usernames of both a and b are the same
are out. This gives the result.
a b
user ip user ip
c 1 a 1
a 1 c 1
The distinct keyword is to eliminate duplicate rows from the result (Which may
be there if in the user table contains the multiple rows with the same username
and ip).
I hope this clarifies.
This all might seem a lot of complexity in the first sight. But once youstart
thinking in SQL way these are quiet straight forward. With the promised
subqueries in the new releases Mysql queries will surely get multiple fold
complex and it would be a great and funny game.
Anvar.
>Thanks!! Worked like a dream! I'm not quite sure why it knew to pull only
>ip's that are in there more than once though?
>
>David
>
>From: "Steve Meyers" <[EMAIL PROTECTED]>
>
>-
>I think I understand. This should work...
>
>select distinct a.username, a.ip from users a, users b where a.ip=b.ip &&
>a.username != b.username;
>
>Steve Meyers
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
