making JOINS

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join
>
nice weekeend
>
Table 1
>
10 5012 10
1600502
100 502 3
1500800 2
>
>
>
>
Table 2
>
1500503 1
14004000
100502 10
100600
100502 3
>

Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||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 502was 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

DA Morgan wrote:

Quote:

Originally Posted by

MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3

>
Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)

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

Quote:

Originally Posted by

>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 502was 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