• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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
0
buktotruth
Asked:
buktotruth
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
buktotruthAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of MySQL are you using?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
buktotruthAuthor Commented:
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 #?
0
 
appariCommented:
try

select t.friendname from friendlist t
left outer join  friendlist i
on i.username = t.friendname
where i.username is null
0
 
ursangelCommented:
try this one

select a.frnd from friend a
where  a.frnd not in (select distinct name from friend b)
0
 
buktotruthAuthor Commented:
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?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have an index on the table friendlist field username?
select t.Friendname
from friendlist as t
where not exists ( select null from friendlist as i where i.username = t.friendname )

Open in new window

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

...thanks for the suggestion.
0
 
buktotruthAuthor Commented:
Thanks for all the help!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now