Hi there,

I'd like to execute an SQL query but i can not find a suitable way.Well i have one table contains :

Table 1 :
Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

Second Table contains :

Table 2 :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

As you see CustId1, CustId2,CustId3 is common.I would like to combine and show them in one table. It is simple with :
select a.* , b.* from Table1 a Table2 b .......(blah blah blah)

I select CustName rows from Table1 and can choose CustId1, CustId2, CustId3.But if i choose CustName (Customer Name) and it returns more than one row i can not match CustId's from Table2 and display in merged Query object.

Let me give an example :

select * from Table1 (where CustName = 'Joe') ;

gives these results :

Id, CustNo, CustName, CustSurName, CustId1, CustId2,CustId3

1,1,Joe,Black,1,1,1
2,2,Joe,Green,1,1,2
3,3,Joe,Blue,1,1,3

select * from Table2

gives these results :

Id, WhNo, WhName, WhSurName, CustId1, CustId2,CustId3

1,1,1,Apple,Peach,1,1,1
1,1,1,Apple2,Peach2,1,1,1
1,1,1,Apple3,Peach3,1,1,2
1,1,1,Apple4,Peach4,1,1,2
1,1,1,Apple5,Peach5,1,1,2
1,1,1,Apple6,Peach6,1,1,3
1,1,1,Apple7,Peach7,1,1,3
1,1,1,Apple8,Peach8,1,1,3
1,1,1,Apple9,Peach9,1,1,3
1,1,1,Apple10,Peach10,1,1,3

As you see 1,1,1,Joe,Black has 2 records in Table2 1,1,2,Joe,Green has 3 records in Table2 finally 1,1,3,Joe,Blue has 5 records in Table2.
Now i would like to select only name in Table1 and display merged table like this :

Table1 and Table 2 : (for example i choose "Joe" from Table 1 and the result is

1,1,Joe,Black,1,1,1,Apple,Peach
1,1,Joe,Black,1,1,1,Apple2,Peach2
2,2,Joe,Green,1,1,2,Apple3,Peach3
2,2,Joe,Green,1,1,2,Apple4,Peach4
2,2,Joe,Green,1,1,2,Apple5,Peach5
.
.
.
(goes like this)

As you see i want to select name from Table 1 and it should give merged table.I only have CustId1,CustId2,CustId3 as common field.

Thanks in advance.

Analyzer

No solution for me eh??

Analyzer

I'd use an INNER JOIN if the instructor has covered them in class.

-PatP