Distinctwhere Exists

Discussion in 'PHP' started by afonseca, Oct 22, 2007.

0
  1. #1
    Hi all!

    I have a small situation and need help.
    I have 2 tables connected: "produto" and "sub_produto". The "sub_produto" table has a id field with the correspondant "produto". But at this moment my "sub_produto" table has several ids of old products that no longer are in "produto" table.
    I need a code in order to retreive ALL id field that exists in "sub_produto" BUT dont exist in "produto" table.
    Not sure if its clear. Please let me know if further esplanition is needed.
    I have tried this but still not good:

    SELECT DISTINCT produto FROM sub_produto
    WHERE EXISTS (SELECT * FROM produto)

    Thanks in advanced.

    António Fonseca
     
    afonseca, Oct 22, 2007 IP
  2. tonybogs

    tonybogs Peon

    Messages:
    462
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Try a NOT EXISTS

    That should do the trick :)
     
    tonybogs, Oct 22, 2007 IP
  3. fric

    fric Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    can also use explicit or implicit inner join

    example:

    SELECT *
    FROM sub_producto
    INNER JOIN produto
    ON sub_producto.produto_id != produto.id

    or

    SELECT *
    FROM sub_producto, produto
    WHERE sub_producto.produto_id != produto.id

    hope this help
     
    fric, Oct 22, 2007 IP