Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple conditions within left join statement

    Hey Guys,

    Issue is as follows:

    1) I've got two tables in my database - one is called 'items', the second one is 'keywords'.
    2) In table 'keywords' there's a field called 'item_id' which will connect the two tables.

    Code:
    items
    id
    name
    
    keywords
    value
    item_id
    What I want to do is run a query that will select items and its attached keywords. Basically the following query will do the job:

    SELECT name FROM items LEFT JOIN keywords ON items.identifier = keywords.item_id

    Problem is that this query will return a record for every unique keyword.value. This is relevant since I want multiple keywords to be attached to a single item.

    Now that I like to select items limited by multiple conditions I won't succeed. For example:

    item1 has 2 keyword values: 'green' and 'red'.
    When i try:

    SELECT name FROM items LEFT JOIN keywords ON items.identifier = keywords.item_id WHERE keywords.value = 'green' AND keywords.value = 'red'

    Nothing's returned. Could anybody tell me how to solve this?
    TIA

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That second query fails because it's impossible for keywords.value to be equal to two different strings at the same time. Try WHERE keywords.value = 'green' OR keywords.value = 'red' instead.

  • #3
    New to the CF scene
    Join Date
    Mar 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know that, but in this case both 'green' and 'red' are attached to this item. My aim is to return that item while both colors are used as filter.

    Do I need subselects or other kind of joins...?


  •  

    Posting Permissions

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