Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Help in Oracle Query!!!

aznwildkataznwildkat Member Posts: 139
so far this is what i've come up with and the code below does not work. Can anyone please help me on how the sytanx of left joins and how to use multiple left joins in a single query in oracle?

SELECT a.*, b.Position_CD, c.Skill_CD, d.Team_Name, d.Team_Country, d.Club, e.Structure_Name
FROM Roster a, Roster_position b, roster_skill c, Team d, Team_Structure e
where (a.Roster_ID = ((b.Roster_ID= c.Roster_Id(+)) b.roster_id(+)).......

I dont' know how to add more left joins!

Here is the query I'm trying to duplicate (which is a query from Ms Access databaase which works fine).

SELECT a.*, b.Position_CD, c.Skill_CD, d.Team_Name, d.Team_Country, d.Club, e.Structure_Name
from ((Roster a LEFT JOIN (Roster_Position b LEFT JOIN Roster_Skill c ON b.Roster_ID=c.Roster_ID) ON a.Roster_ID=b.Roster_ID) LEFT JOIN Team d ON a.Team_CD=d.Team_CD) LEFT JOIN Team_Structure e ON a.Team_Structure_CD=e.Team_Structure_CD
ORDER BY a.Roster_Id;

Any help or comments are greatly appreciated

Comments

  • infidelinfidel Member Posts: 2,900
    : so far this is what i've come up with and the code below does not work. Can anyone please help me on how the sytanx of left joins and how to use multiple left joins in a single query in oracle?
    :
    : SELECT a.*, b.Position_CD, c.Skill_CD, d.Team_Name, d.Team_Country, d.Club, e.Structure_Name
    : FROM Roster a, Roster_position b, roster_skill c, Team d, Team_Structure e
    : where (a.Roster_ID = ((b.Roster_ID= c.Roster_Id(+)) b.roster_id(+)).......
    :
    : I dont' know how to add more left joins!

    I'm not sure I understand what you're trying to do. The names of your tables make me think you don't have your database schema in a normalized form. Or maybe you do. Hard to tell with such little information.

    You might try this:

    select a.*, b.Position_CD, c.Skill_CD, d.Team_Name, d.Team_Country, d.Club, e.Structure_Name
    FROM Roster a, Roster_position b, roster_skill c, Team d, Team_Structure e
    where a.roser_id = b.roster_id (+)
    and a.roser_id = c.roster_id (+)
    and a.roser_id = d.roster_id (+)
    and a.roser_id = e.roster_id (+)

    That's only a guess based on what it appears to me that you're trying to do.

    Just for kicks, could you post the schema of these tables? If not, that's cool.
  • aznwildkataznwildkat Member Posts: 139

    : Just for kicks, could you post the schema of these tables? If not, that's cool.
    :

    Well i can tell you how its setup. And yes I did normalize this.

    8 Tables: Roster, Roster_Position, Position, Skill, Roster_Skill, Team, Team_Structure, Ethnicity

    A person in the roster can have multiple positions and skills. That is why i made different tables. The Position and Skill table defines the values that are allowed. The way i setup the database seems a little awkward but its the best way i could think of setting up the database.

Sign In or Register to comment.