Three table join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Three table join

  1. #1
    Join Date
    Oct 2004
    Posts
    8

    Three table join

    Hi,

    I have some troube with my queries
    Here is how the table looks like:

    S
    _
    snr(primary key)
    sname
    status
    city

    P
    _

    pnr(primary key)
    pname
    color
    weight
    city

    SP
    __
    snr (primary + foreign key)
    pnr (primary + foreign key)
    qty

    What I want to do is to find which suppliers(S) supply blue parts(P)

    I know I have to join s, p, sp since I need all the tables. I also know I need to use where p = 'blue'

    I tried this query:

    select s.sname, p.pname
    2 from s natural join sp natural join p
    3 on sp.snr = s.snr
    4 and sp.pnr = p.pnr
    5 where p.color = 'blue'

    ERROR at line 3:
    ORA-00933: SQL command not properly ended


    but is doesn't work.
    I'm using Oracle 10g.

    Please help me

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Try:

    select s.sname, p.pname
    from s
    join sp on sp.snr = s.snr
    join p on sp.pnr = p.pnr
    where p.color = 'blue'

  3. #3
    Join Date
    Oct 2004
    Posts
    8
    Thanks.

    It's working now

Posting Permissions

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



Click Here to Expand Forum to Full Width