SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    TN
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help Selecting from 1 table only records that match multiple records in another

    I am having trouble writing an SQL statement to select all the records in Table-A that have multiple corresponding records in Table-B that meet a requirement.

    Example:
    names
    id | name
    1 | bob
    2 | chris
    3 | sam

    favoritecolors
    id | nameid | color
    1 | 1 | blue
    2 | 1 | yellow
    3 | 2 | blue

    I would like to select all the name records of people that like both blue AND yellow, not just blue OR yellow. So the output of the select should be the record for bob and nobody else.

    I figure a join of some kind would be used but I can't quite figure it out.

    This works if i'm only looking for people that like one color:
    Code:
    SELECT names.id, names.name FROM names JOIN favoritecolors ON names.id = favoritecolors.nameid WHERE favoritecolors.color = 'blue'
    This is what i'm trying to do to get people that like 2 different colors but it's wrong:
    Code:
    SELECT names.id, names.name FROM names JOIN favoritecolors ON names.id = favoritecolors.nameid WHERE favoritecolors.color = 'blue' AND favoritecolors.color = 'yellow'
    -Mike

    - In the mood for some funny pictures or videos?
    - Find the best websites and share yours.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT n.name
      FROM `favorite-colors-table` AS f
    INNER
      JOIN `names-table` AS n
        ON n.id = f.nameid
     WHERE f.color IN ( 'blue','yellow' )
    GROUP
        BY n.name
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    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
  •