Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: Join Two Tables while excluding some results

    I cant seem to figure this one out for MS Access. Here is some details

    Version MS Access 2010

    Tbl1 has 1000 records
    Tbl2 has 5000 records
    Tbl2 has many records that match to one row in Tabl1
    I want to Select only rows from Tbl1 that do not have any rows matching a given value in Tbl2

    EX
    Products contains Bikes,Cars,skateboards
    Colors contains matches for Blue cars, Green bikes, Black cars, Red bikes, red cars, red skateboards, black skateboards

    Ex
    SELECT p.productID, p. ProductName, c.color
    FROM products AS p
    LEFT JOIN productColors AS c
    ON p.id = c.id

    I want to select only products that don’t come in the color green, but with a query I get red bikes in the recordset.. but I don’t want any bikes if there is even one row in the db with a green bike.

    Any pointers would be great! Thanks!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, i think your join's ON condition is wrong

    product id and color id would never be equal (except coincidentally)

    what's more likely is that you have a column called color_id in the product table

    so the join would be on c.id = p.color_id

    to find all products that don't come in green, do this --
    Code:
    SELECT p.productID
         , p. ProductName
         , c.color
      FROM products AS p
    LEFT OUTER
      JOIN productColors AS c
        ON c.id = p.color_id
     WHERE NOT EXISTS
           ( SELECT 'uh oh'
               FROM productColors
              WHERE id = p.color_id
                AND name = 'green' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    6
    Yes, this is just a made up table to understand how to do it... but you are right it should be more like the following.

    p.id = c.fk_productID

    so...

    product table goes like this

    id = 1 product = car
    id = 2 product = bike
    id = 3 product = skateboard

    productColor table
    id = 1 fk_productID = 1 color = red
    id = 2 fk_productID = 1 color = blue
    ect..

  4. #4
    Join Date
    Mar 2012
    Posts
    6
    SELECT p.id, c.color
    FROM products AS p
    LEFT OUTER JOIN productColor AS c
    ON p.id = c.fk_productID
    WHERE p.online = "true"
    AND NOT EXISTS (SELECT color
    FROM productColor
    WHERE color = <cfqueryparam value="green">
    AND p.id = c.fk_productID
    )

    It worked! Thanks!!
    Last edited by jayhawk1; 04-14-12 at 19:52.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey another cf guy!

    glad i could be of help
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2012
    Posts
    6
    Yeah CF! Didn't even notice I put that in there... habitual I guess.

Posting Permissions

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