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

    Unanswered: [Req]:SQL Query Help

    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
    Last edited by Analyzer; 06-20-04 at 04:50.

  2. #2
    Join Date
    Apr 2004
    Posts
    38
    No solution for me eh??

    Analyzer

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use an INNER JOIN if the instructor has covered them in class.

    -PatP

Posting Permissions

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