1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Join on three tables

Discussion in 'SQL PL/SQL' started by mukulverma2408, Oct 10, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi Experts,

    I am really confused and need your help on understanding join on three or more tables, here is the scenario i am working on, i have three tables namely :
    1. person
    2. pet
    3. person_pet
    Code (Text):

    select * from person :
    personid,personname
    1,mukul
    2,majaya
    3,vishnu
    4,nikun
    5,anujam
     
    select * from pet
    Code (Text):

    select * from pet
    petid,petname
    1,dog
    2,cat
    3,horse
    4,cow
     
    Code (Text):

    select * from person_pet
    personid,petid
    1,3
    2,4
    5,1
    3,2
    2,3
    1,4
     
    Now i want to know the name of the person and pet owned by him, below are two query for same task
    Code (Text):

    SELECT b.ppname,a.petname
      FROM pet a JOIN
        (SELECT p.personname ppname ,pp.petid pppid
           FROM person p
           JOIN person_pet pp ON p.personid=pp.personid) b
     ON pppid=a.petid
     
    Code (Text):

    SELECT p.personname,pet.petname
     FROM person p
     JOIN person_pet pp ON p.personid=pp.personid
     JOIN pet ON pp.petid = pet.petid
     
    I want to know the working of second query, how does it joined the tables correctly without using any alias from first join
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are aliases used:

    person is aliased as 'p'
    person_pet is aliased as 'pp'
    pet is such a short name it isn't aliased

    Therefore there is no confusion within Oracle as to where the column values are to be taken from.
     
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    I think i was not clear, let me try to explain it further :

    Let's break the second query in two part :

    Part1 - SELECT p.personname,pet.petname
    FROM person p
    JOIN person_pet pp ON p.personid=pp.personid

    Part2 - JOIN pet ON pp.petid = pet.petid

    How does second part works??

    Does the second JOIN always works on output of first joins??
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I'm not positive that I understand the intent of your question, but two things you have said in your posts concern me about your understanding of JOIN operations:

    Post 1: "....without using any alias from first join..."
    Post 2: "...Does the second JOIN always works on output of first joins??"

    When performing multiple JOIN operations, successive JOINs do not 'take the output' of previous JOIN operations and use them as input. A JOIN is between two tables (or views, materialized views, etc.) Take your JOIN operations:

    Code (Text):
     FROM person p
    JOIN person_pet pp ON p.personid=pp.personid
    JOIN pet ON pp.petid = pet.petid
    If I were to write the logic out in English, it would be something like:

    Pre-Join: Take the PERSON table and call it P.
    First Join: Join the PERSON_PET table (which we will call PP) to the PERSON table using the PERSONID column from the PERSON table and the PERSONID column from the PERSON_PET table.
    Second Join: Join the PET table to the PERSON_PET table using the PETID column from PERSON_PET and the PETID column from PET.

    Later joins do not reference the OUTPUT earlier joins. They do reference TABLES that were introduced in earlier joins. The JOIN statement only references the new table being added. Oracle determines the existing table that it is being joined with based on the columns supplied.

    In the below example (where I have removed table aliases to make the example simpler), I swapped the order of the two JOINs. Now the first JOIN generates an error because it is referencing the PERSON_PET table, but that table is not yet part of the JOIN operation. When Oracle tries to find an existing table that matches the PERSON_PET.PETID column, it fails.

    Code (Text):

    SELECT person.personname, pet.petname
    FROM person
    JOIN pet ON person_pet.petid = pet.petid
    JOIN person_pet ON person.personid = person_pet.personid;

    SQL Error: ORA-00904: "PERSON_PET"."PETID": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
     
  5. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Matthew, that was very helpful, thanks for taking out your time to explain it in detail :)
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Matthew gave an excellent response, one you would have received had you asked this version of your question in the first place. It helps tremendously to ask clear questions; the more detail you can provide the better the response will be. Please read here:

    https://dfitzjarrell.wordpress.com/2015/08/26/but-you-dont-understand/

    I think it may help you understand the forum from both your perspective and from that of the volunteers.