Help with a query

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

May 3rd, 2008, 08:26 PM

Invalid02

Contributing User

Join Date: Jan 2005

Posts: 213

Time spent in forums: 3 Days 2 h 8 m 21 sec
Reputation Power: 11

Help with a query

Last edited by Invalid02 : May 3rd, 2008 at 09:18 PM.

May 3rd, 2008, 09:03 PM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

how are you storing the friends relationship? if i find a row with Will | George will i also find a row with George | Will? if not, why not?

does "direction" matter?

__________________
r937.com | rudy.ca

May 3rd, 2008, 09:18 PM

Invalid02

Contributing User

Join Date: Jan 2005

Posts: 213

Time spent in forums: 3 Days 2 h 8 m 21 sec
Reputation Power: 11

Quote:

Originally Posted by r937

how are you storing the friends relationship? if i find a row with Will | George will i also find a row with George | Will? if not, why not?

does "direction" matter?

Sorry that's my mistake - there is a row for George | Will as well as Will | Gearoge - I've updated me previous post.

What do you mean by direction?

Thanks

May 3rd, 2008, 10:11 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

Quote:

Originally Posted by Invalid02

What do you mean by direction?

if there were only one row, this would imply a direction -- George is a friend of Will

but since there are two rows, there is no direction, or, more correctly, there are both directions -- George is a friend of Will, and Will is a friend of George

Quote:

Originally Posted by Invalid02

I need a query that will return all the usernames/names that Will is not friends with

Code:

SELECT 
  FROM users
LEFT OUTER
  JOIN friends
    ON users.name IN ( friends.username, friends.friend_username)
 WHERE users.name = 'Will'
   AND friends.username IS NULL

May 3rd, 2008, 10:30 PM

Invalid02

Contributing User

Join Date: Jan 2005

Posts: 213

Time spent in forums: 3 Days 2 h 8 m 21 sec
Reputation Power: 11

Quote:

Originally Posted by r937

Code:

SELECT 
  FROM users
LEFT OUTER
  JOIN friends
    ON users.name IN ( friends.username, friends.friend_username)
 WHERE users.name = 'Will'
   AND friends.username IS NULL

This doesn't seem to return me any rows - I'm confused with the lines:

ON users.name IN ( friends.username, friends.friend_username)
WHERE users.name = 'Will'

There will never be a users.name in either friends.username of friends.friend_name and users.name would be William Brown not Will. Did you perhaps mean:

Code:

SELECT users.username, users.name
  FROM users
LEFT OUTER
  JOIN friends
    ON users.username IN ( friends.username, friends.friend_username)
 WHERE users.username = 'Will'
   AND friends.username IS NULL

Although, this still doesn't return me any rows...

Thanks for your time.

May 3rd, 2008, 10:41 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

aaak!! what i should have said is

Code:

SELECT 
  FROM users
LEFT OUTER
  JOIN friends
    ON users.username  IN ( friends.username, friends.friend_username)
 WHERE users.username  = 'Will'
   AND friends.username IS NULL

Last edited by r937 : May 3rd, 2008 at 10:44 PM.

May 3rd, 2008, 11:02 PM

Invalid02

Contributing User

Join Date: Jan 2005

Posts: 213

Time spent in forums: 3 Days 2 h 8 m 21 sec
Reputation Power: 11

Quote:

Originally Posted by r937

aaak!! what i should have said is

Code:

SELECT 
  FROM users
LEFT OUTER
  JOIN friends
    ON users.username  IN ( friends.username, friends.friend_username)
 WHERE users.username  = 'Will'
   AND friends.username IS NULL

I'm still having problems I'm afraid. My complete query looks like this:

Code:

SELECT users.username 
FROM users 
LEFT OUTER JOIN friends 
ON users.username IN ( friends.username, friends.friend_username ) 
WHERE users.username = 'Will' AND friends.username IS NULL

Could the problem be that my friends.username field, when created, was set to NOT NULL?

Thanks again.

May 4th, 2008, 07:11 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,727

Time spent in forums: 1 Month 3 Weeks 2 Days 19 h 22 m 42 sec
Reputation Power: 848

oh man, i'm sorry, i really messed up last night

i tested this, so i know it works:

Code:

SELECT users.*
  FROM users 
LEFT OUTER
  JOIN friends
    ON friends.username = users.username AND friends.friend_username = 'Will' 
    OR friends.username = 'Will' AND friends.friend_username = users.username 
 WHERE users.username <> 'Will'
   AND friends.username IS NULL

there are two lines in the ON clause, you don't really need them both because your friendships are bidirectional and you store both rows

May 4th, 2008, 08:41 AM

Invalid02

Contributing User

Join Date: Jan 2005

Posts: 213

Time spent in forums: 3 Days 2 h 8 m 21 sec
Reputation Power: 11

Quote:

Originally Posted by r937

oh man, i'm sorry, i really messed up last night

i tested this, so i know it works:

Code:

SELECT users.*
  FROM users 
LEFT OUTER
  JOIN friends
    ON friends.username = users.username AND friends.friend_username = 'Will' 
    OR friends.username = 'Will' AND friends.friend_username = users.username 
 WHERE users.username <> 'Will'
   AND friends.username IS NULL

there are two lines in the ON clause, you don't really need them both because your friendships are bidirectional and you store both rows

Hehe, this works perfectly now - thank you very much for your persistence!

Viewing: Dev Shed Forums > Databases > MySQL Help > Help with a query

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!