Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unhappy Unanswered: Help with select statement

    Hello I am quite new to SQL and I would like some help with a simple select statement in oracle.

    I have a table called (tbl2) which has a column called name which can hold full name whether that is customer or a supplier.

    Now I am writing a select statement on two different tables
    and I want to query on the same column name twice but with different where clauses:


    select
    tbl1.id ID,
    tbl2.name SUPPLIER,
    tbl2.name CUSTOMER,
    tbl1.address ADDRESS,

    FROM tbl1,tbl2, WHERE

    tbl2.id = tbl1.suplid
    and tbl2.id = tbl1.custid


    Now the above statement doesn't work and returns no records back. I believe that is because it doesn't flush the value of tbl2.id so I thought to do the following:

    select
    tbl1.id ID,
    tbl2.name SUPPLIER,
    (select tbl2.name from tbl2 where tbl2.id = tbl1.custid ) CUSTOMER,
    tbl1.address ADDRESS,

    FROM tbl1,tbl2, WHERE

    tbl2.id = tbl1.suplid

    Now that worked fine but I was wondering if could it the first way somehow.

    Please help ....


    Fred

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Double join your table....

    select
    tbl1.id ID,
    supl.name SUPPLIER,
    cust.name CUSTOMER,
    tbl1.address ADDRESS
    FROM tbl1,tbl2 supl, tbl2 cust
    WHERE supl.id = tbl1.suplid
    and cust.id = tbl1.custid

    Greetz

  3. #3
    Join Date
    Feb 2005
    Posts
    2

    Smile

    Namliam

    Thank you very much for your reply. That worked fine

    Fred

Posting Permissions

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