[Analyzer]

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

[LKBrwn_DBA]

Try this:

Select a.Id, a.CustNo, a.CustName, a.CustSurName
     , a.CustId1, a.CustId2, a.CustId3
     , b.WhName, b.WhSurName
  From Table1 a, Table2 b
 Where b.CustId3 = a.CustId3;

[Analyzer]

Hi,

Thank you for your reply but i can not select my list with this code because first of all i would like to select by name then Table 1 will return CustId numbers.Finally i'd like to make a merged list which contains Table1+Table2 values
Pat adviced me to use inner join for this.What is it?

Analyzer

P.S. By the way i didnt see an SQL section in the forum and i cross posted.I apologize for this.

[LKBrwn_DBA]

Which RDBMS software are you using?

[Analyzer]

Well i am using MySQL 4.0+Borland C++ Builder 6.0 Ent+Zeos DBO 6.1.5

[Pat Phelan]

Try something like:

SELECT *
   FROM table1
   INNER JOIN table2
      ON (table2.custId1 = table1.custId1
      AND table2.custId2 = table1.custId2
      AND table2.custId3 = table1.custId3)

-PatP

[Analyzer]

Hi Pat,

Thank you for your reply.But first i want to search on Customer Name then i would like to select columns from table2.I wrote this code :

SELECT * FROM table1 WHERE (CustName="John" and CustSurName="Blue") INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3)

But it does not work.Where is my fault?
I would like to explain more.For example John Blue purchased a printer, 2 DSL Modem and 1 monitor.I would like to search who named "John" and what did he purchased.
First of all i need to search name then i will search on CustId1,CustId2,CustId3.

I found this page : http://www.w3schools.com/sql/sql_join.asp but it does not elaborate.

Analyzer.

[Analyzer]

Ok, i did it

SELECT * FROM table1 INNER JOIN table2 ON (table1.CustId1 = table2.CustId1 AND table1.CustId2 = table2.CustId2 AND table1.CustId3 = table2.CustId3) WHERE (CustName="John" and CustSurName="Blue")

Thank you for your all help.I appreciated it much.

Analyzer