Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: Really confused in SQL statement

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Try this:
    Code:
    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;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2004
    Posts
    38
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Which RDBMS software are you using?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2004
    Posts
    38
    Well i am using MySQL 4.0+Borland C++ Builder 6.0 Ent+Zeos DBO 6.1.5

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try something like:
    Code:
    SELECT *
       FROM table1
       INNER JOIN table2
          ON (table2.custId1 = table1.custId1
          AND table2.custId2 = table1.custId2
          AND table2.custId3 = table1.custId3)
    -PatP

  7. #7
    Join Date
    Apr 2004
    Posts
    38
    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.

  8. #8
    Join Date
    Apr 2004
    Posts
    38
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •