asked on

Help with an SQL query

I need help constructing a query.

My table has two columns: [Name] and [Friend]. The [Friend] column has a list of friends associated with each person in [Name]. An example:

Name Friend
Joe Bob
Joe Steve
Bill Doug
Bill Steve
Steve Adam

So a person can have multiple instance in both the [name] and [friend] columns. What I want to do is have a query that outputs all the people in [friend] who are not already in [name]. The idea is that for each person in [name] i have a list of friends. Sometimes those friends exist in [name] and sometimes they don't.

Oh, I'm running MySQL.

Thanks a log

Guy Hengel [angelIII / a3]

here we go:

select t.Friend
from yourtable t
where not exists ( select null from yourtable i where i.name = t.friend )

Open in new window

buktotruth

ASKER

Tried as you described with actual column names:

select t.friendname from friendlist t where not exists ( select null from friendlist i where i.username = t.friendname )

got this error:
You have an error in your SQL syntax near 'exists ( select null from friendlist i where i.username = t.friendname )' at line 1

Guy Hengel [angelIII / a3]

what version of MySQL are you using?

buktotruth

ASKER

Good question. I'm running off of a remote server, and i'm not entirely sure how to check. I ran phpinfo() and found that the MySQL API version is 5.0.22. Does that help? If not, how do I figure out the version #?

appari

try

select t.friendname from friendlist t
left outer join friendlist i
on i.username = t.friendname
where i.username is null

ursangel

try this one

select a.frnd from friend a
where a.frnd not in (select distinct name from friend b)

buktotruth

ASKER

ursnagel->same error as i was getting in the first place

appari-> that seems to work but it is VERY slow. My table has ~81,000 entries and when i run the command it just hangs. I tried looking at just the first 1500 entries to see how long it would take, and even that took about a minute. Is there any faster way to do this?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

buktotruth

ASKER

Good point...building the index now...it'll take a while. Will let you know if that speeds things up.

...thanks for the suggestion.

buktotruth

ASKER

Thanks for all the help!