SQL Syntax Question??? - Macromedia Dreamweaver

[Kory]

I have 2 tables. One that has all my products (table1) and another table
that matches my products with a invoice number (table2).

What I want to do is extract all the products that don't belong to a invoice
based on the info in table2.

Basically I want to extract all the products that have never been ordered.
Here is the join I have but it doesn't seem to work.

SELECT *
FROM Lots, ShipLots
WHERE Lots.ID <> ShipLots.ShipmentID
ORDER BY Lots.ID DESC

Thanks

[craig v]

This should work for you:

SELECT ID
FROM LOTS
WHERE ID NOT IN (SELECT ShipmentID FROM ShipLots)
ORDER BY ID DESC

This will return the ID's that are not in the second table.

Let me know if that does the trick.

craig v



"Kory" <koryc5designs.com> wrote in message
news:bekm8c$lqk$1forums.macromedia.com...
I have 2 tables. One that has all my products (table1) and another table
that matches my products with a invoice number (table2).

What I want to do is extract all the products that don't belong to a invoice
based on the info in table2.

Basically I want to extract all the products that have never been ordered.
Here is the join I have but it doesn't seem to work.

SELECT *
FROM Lots, ShipLots
WHERE Lots.ID <> ShipLots.ShipmentID
ORDER BY Lots.ID DESC

Thanks

[Kory]

that did it!

Thanks

"craig v" <craig765spamless-> wrote in message
news:bekpp7$r7i$1forums.macromedia.com...

> This should work for you:
>
> SELECT ID
> FROM LOTS
> WHERE ID NOT IN (SELECT ShipmentID FROM ShipLots)
> ORDER BY ID DESC
>
> This will return the ID's that are not in the second table.
>
> Let me know if that does the trick.
>
> craig v
>
>
>
> "Kory" <koryc5designs.com> wrote in message
> news:bekm8c$lqk$1forums.macromedia.com...
> I have 2 tables. One that has all my products (table1) and another table
> that matches my products with a invoice number (table2).
>
> What I want to do is extract all the products that don't belong to a

invoice

> based on the info in table2.
>
> Basically I want to extract all the products that have never been ordered.
> Here is the join I have but it doesn't seem to work.
>
> SELECT *
> FROM Lots, ShipLots
> WHERE Lots.ID <> ShipLots.ShipmentID
> ORDER BY Lots.ID DESC
>
> Thanks
>
>
>