SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Nov 2002
    Location
    Maryland
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with SQL Statement

    Both of these SQL statements return results:

    Code:
    SELECT person_id, person_first, person_last
    FROM people, people_expertise, expertise2people
    WHERE expertise2people.person_lookup_id = people.person_id 
    AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
    AND expertise_lookup_id = 5
    GROUP BY person_id
    Code:
    SELECT person_id, person_first, person_last
    FROM people, people_expertise, expertise2people
    WHERE expertise2people.person_lookup_id = people.person_id 
    AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
    AND expertise_lookup_id = 6
    GROUP BY person_id
    How come when I combine the expertise I get no results?

    Code:
    SELECT person_id, person_first, person_last
    FROM people, people_expertise, expertise2people
    WHERE expertise2people.person_lookup_id = people.person_id 
    AND expertise2people.expertise_lookup_id = people_expertise.expertise_id 
    AND expertise_lookup_id = 5 AND  expertise_lookup_id = 6
    GROUP BY person_id
    I know there are people in the DB with both expertise 5 and 6. I want only those people. Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by JeffGSR View Post
    How come when I combine the expertise I get no results?
    because of this piece of code --
    Code:
    AND expertise_lookup_id = 5 AND  expertise_lookup_id = 6
    WHERE conditions are evaluated on a row by row basis

    there's no way that expertise_lookup_id can have a value that is simultaneously equal to two different numbers

    what you want is this --
    Code:
    AND expertise_lookup_id = 5 OR expertise_lookup_id = 6
    and it would be better to write it like this --
    Code:
    AND expertise_lookup_id IN (5,6)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •