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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SQL Joins (3 Tables)

    I'm trying with all my might to get my head around this problem but cannot.

    I have three tables

    Table A [Stores]
    [id][name]
    [1][Store #1]
    [2][Store #2]
    [3][Store #3]
    [4][Store #4]

    Table B [Brands]
    [id][name]
    [1][Brand #1]
    [2][Brand #2]
    [3][Brand #3]
    [4][Brand #4]

    Table C [Stores_Brands]
    [store_id][brand_id]
    [1][3]
    [3][3]
    [4][3]

    What I would like to do is list highlight the stores that stock Brand #3, in this case Stores #1,#3 and #4.
    What I want to pull out of the database is something like the below

    [store_id][brand_id]
    [1][3]
    [2][NULL]
    [3][3]
    [4][3]

    Is it possible?

    Thank you

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Trivial. You just need to learn about "outer joins".

    Code:
    SELECT S.store_id, S.name AS storename, B.brand_id, B.name AS brandname
    FROM stores AS S
    LEFT JOIN store_brands AS SB ON SB.store_id = S.store_id
    LEFT JOIN brands AS B ON B.brand_id = SB.brand_id AND B.brand_id = 3
    In this case, you could also do it as
    Code:
    SELECT S.store_id, S.name AS storename, B.brand_id, B.name AS brandname
    FROM stores AS S
    LEFT JOIN store_brands AS SB ON SB.store_id = S.store_id AND SB.brand_id = 3
    LEFT JOIN brands AS B ON B.brand_id = SB.brand_id
    That is, the restriction on brand_id can be applied to either join/table.

    Note that you CAN NOT put that condition in the WHERE clause. It MUST be in the ON clause.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    darrylm (06-25-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Just for completeness, here's another way you could do it. A little less intuitive, but it works:
    Code:
    SELECT S.store_id, S.name AS storename, B.brand_id, B.name AS brandname
    FROM brands AS B
    INNER JOIN store_brands AS SB ON B.brand_id = SB.brand_id AND B.brand_id = 3
    RIGHT JOIN stores AS S ON SB.store_id = S.store_id
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    darrylm (06-25-2013)

  • #4
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    9
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The first solution I'd managed before whilst playing with outer joins, but it was returning all records for all brands.

    The second and third solutions however, are perfect.

    I've been having trouble with anything other than simple inner joins mostly therefore this should go some way to understanding a little more, thank you.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Surprised the first one doesn't work. Now you're going to make me actually try it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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