+ Reply to Thread
Results 1 to 2 of 2

Thread: Find any field without a related key

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    40

    Find any field without a related key

    I've got a table called product with extra key, values used for analytics and stuff. My problem is that if it's easy to find the product with the specified key, it's much more tricky to find the one without the key , here is my code :
    Code:
    SELECT products.*
        FROM products
        JOIN info ON products.id = info.product_id
        WHERE
            info.key = 'smkey' AND
            info.value = '1'
    I'll get all the products with the mykey key specified and set to the value 1

    Now I'd like to get all the products without the key mykey set (the 2 in the example below). No elements exist in the info table if the key isn't set.
    Code:
    +-------------+     +----------------------------+
    | products    |     |             info           |
    +=============+     +============+=======+=======+
    |id           |     | product_id | key   | value |
    +-------------+     +------------+-------+-------+
    | 1           |     | 1          | smkey | 1     |
    | 2           |     | 1          | foo   | bar   |
    +-------------+     +------------+-------+-------+

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    30
    Using NOT EXISTS would be the ideal solution .
    Code:
    SELECT p.*
    FROM   products as p
    WHERE  NOT EXISTS (SELECT  m.product_id
                       FROM    info AS i
                       WHERE   i.key = 'mykey'
                               AND i.value = '1'
                               AND i.product_id = p.product_id)

+ Reply to Thread

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