Posted by Hugo Kornelis on 10/25/06 21:53

On 23 Oct 2006 00:52:26 -0700, MORALBAROMETER wrote:

>Hi Daniel,
>thanks alot for the prompt reply. this is what i had:
>
>100 502
>100 502
>1500 800
>
>as seen in the table 100 502 was ordered and shipped but more articles
>were shipped not ordered by customerID(100). I do not need this
>information. I need a list where a customer ordered and received
>shippment where no product ordered was shipped. in this case
>customer(1500) should be the right answer. He ordered and recived
>shippement but within the list no article oredered was shiped. but
>customer (100) received the ordered product plus some extra
>Hope to read from u and any one else

Hi MORALBAROMETER,


Maybe something like this?

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
LEFT JOIN Shipments AS s
ON s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID
WHERE s.CustomerID IS NULL

or the following (logically equivalent, but easier to understand for
beginning SQL coders):

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
WHERE NOT EXISTS
(SELECT *
FROM Shipments AS s
WHERE s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID)

If these are not what you're after, then I recommend that you post your
table structure (as CREATE TABLE statements, including constraints,
properties and indexes), some rows of sample data (as INSERT statements)
and expected results. See www.aspfaq.com/5006 for more info.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]