SQL joins

[Can]

Hi all,

No matter how hard I've tried and researched, I just cannot figure out
how to do the following. I kindda think there must be an easy way.

I want to join two tables.
Table 1:
C1 C2 C3
A X1 Y
A X2 Z
A X3 Y

Table2:
C1 C2 C3
A N Y
A M Z
A N Y

So I do:
select i1.*, i2.* from Table1 i1
join Table2 i2
on i1.C3 = i2.C3

And I get

A X1 Y A M Y
A X3 Y A M Y
A X1 Y A N Y
A X3 Y A N Y
A X2 Z A N Z

But I want

A X3 Y A M Y
A X1 Y A N Y
A X2 Z A N Z

Is there a way to achieve this with SQL joins? Or any other way?
Thanks already...
Can

[MC]

Please post DDL and inserts. When you do that, tell us what are the
additional requierements/filters. For example, why is this row 'invalid':
A X1 Y A M Y
how do you know you dont want that one and that you want this one:

A X1 Y A N Y

Using X1 as an example...

"Can" <ce***@hotmail.com> wrote in message
news:6d*************************@posting.google.co m...

Hi all,

No matter how hard I've tried and researched, I just cannot figure out
how to do the following. I kindda think there must be an easy way.

I want to join two tables.
Table 1:
C1 C2 C3
A X1 Y
A X2 Z
A X3 Y

Table2:
C1 C2 C3
A N Y
A M Z
A N Y

So I do:
select i1.*, i2.* from Table1 i1
join Table2 i2
on i1.C3 = i2.C3

And I get

A X1 Y A M Y
A X3 Y A M Y
A X1 Y A N Y
A X3 Y A N Y
A X2 Z A N Z

But I want

A X3 Y A M Y
A X1 Y A N Y
A X2 Z A N Z

Is there a way to achieve this with SQL joins? Or any other way?
Thanks already...
Can

[David Portas]

What is the relationship between the two tables? Apparently the only
thing you have to join them on is C3 but that doesn't give us (or SQL
Server) enough information to solve your problem. How is SQL supposed
to know which row from Table1 to join to which SINGLE row in Table2?
You seem to be basing the result you want on an undefined order of
precedence in the rows to be joined but there is no column to tell us
that information and tables in SQL have no inherent order at all.

Also, I think you may have made an error in your sample data. There is
no (A,M,Y) row in Table2. This article explains the best way to
describe your problem to help others to help you:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--

[Data]

going a bit further, given the data you presented It seems there is an error
in your output as well.
Changing the data per david's comment as follows
C1 C2 C3
A X1 Y
A X2 Z
A X3 Y

Table2:
C1 C2 C3
A N Y
A M Z
A M Y -- changed N to M

Here is your original requested output
A X3 Y A M Y
A X1 Y A N Y
A X2 Z A N* Z

The row A,M,Z will match with A,X2,Z but you are asking for A,N,Z
assuming this is a mistake as well and changing to this
A X2 Z A M* Z

It is still not possible to restrict the rows on a natural alignment of
data... ie y=y.
you have to add something extra to the join to specify, when t1.c3 is Y only
join to t2 where c3's are equal
AND match X1 to N and X3 to M
This is do-able but Quite ugly

It might be better to add another column so you can spell out this relation
ship more naturally.
Kevin Ruggles

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Also, I think you may have made an error in your sample data. There is
no (A,M,Y) row in Table2. > -- David Portas
SQL Server MVP
--