new to oracle, need to join tables to get the field values in my resultset

i know sql server and not oracle, so i will speak in sql server language to describe what i need from oracle.

i have a oracle query i am developing that needs to select the following fields detailed below. i found all but two of them in the BUG table. the other two are in other tables that i am not clear on how to get into my oracle SQL.

also i want to convert the field names defined in Oracle to field names that are more meaningful to me and indecently the dame as the field names in my sql table. (this is part of an oracle extract/ sql2005 insert job) this may need to be oricalafied as well cause im writing it the sql way and just expecting it to work... let me know.

my sql so far- i added '' as placeholders for the 2 fields i need to join to:

BG_SUBJECT field is part of the ALL_LISTS Table, where AL_ITEM_ID is the primary key.

DetectedInRelease is the REL_NAME field in the RELEASES table where REL_ID is the primary key.

SELECT 
bg_user_56 AS Project, 
bg_user_60 AS SubSystem, 
BG_USER_81 AS AssignedToUserName, 
bg_responsible AS AssignedTo,  
bg_status AS Status, 
BG_USER_15 AS BusinessFunction, 
bg_detection_date AS DetectedOnDate, 
BG_SEVERITY AS BusinessSeverity,  
bg_user_36 AS TestingSeverity, 
bg_bug_id AS DefectID, 
Bg_User_09 AS EstFixedDate,
bg_user_25 AS EstReadyForRetest,
BG_DESCIPTION AS description, 
BG_USER_03 AS DetectedInDeploymentEvent, 
'' AS DetectedInRelease,--- ??? not in bug table !!!!
BG_USER_47 AS FunctionalAreaWorkstream,
BG_USER_19 AS PlannedFixInDeploymentEvent, 
BG_USER_55 AS PlannedFixInRelease,
BG_USER_57 AS PTMTestManager, 
Bg_User_58 AS RemediatingCTOName, 
'' AS Subject,--- ??? not in bug table !!!
bg_summary AS Summary, 
bg_user_80 AS MLTestEnvironment, 
GETDATE() AS L开发者_StackOverflow中文版oadDateTime,
bg_user_12 AS Deferred 
FROM tascs_ml_bac_transition_db.BUG

the query syntax is effectively the same, GETDate will need to be SYSDATE however, included as a sample inner join

SELECT 
        BUG.bg_user_56         AS Project                    ,
        BUG.bg_user_60        AS SubSystem                  ,
        BUG.BG_USER_81        AS AssignedToUserName         ,
        BUG.bg_responsible    AS AssignedTo                 ,
        BUG.bg_status         AS Status                     ,
        BUG.BG_USER_15        AS BusinessFunction           ,
        BUG.bg_detection_date AS DetectedOnDate             ,
        BUG.BG_SEVERITY       AS BusinessSeverity           ,
        BUG.bg_user_36        AS TestingSeverity            ,
        BUG.bg_bug_id         AS DefectID                   ,
        BUG.Bg_User_09        AS EstFixedDate               ,
        BUG.bg_user_25        AS EstReadyForRetest          ,
        BUG.BG_DESCIPTION     AS description                ,
        BUG.BG_USER_03        AS DetectedInDeploymentEvent  ,
        REL.REL_NAME          AS DetectedInRelease          , --- ??? not in bug table !!!!
        BUG.BG_USER_47        AS FunctionalAreaWorkstream   ,
        BUG.BG_USER_19        AS PlannedFixInDeploymentEvent,
        BUG.BG_USER_55        AS PlannedFixInRelease        ,
        BUG.BG_USER_57        AS PTMTestManager             ,
        BUG.Bg_User_58        AS RemediatingCTOName         ,
        al.BG_SUBJECT         AS Subject                    , --- ??? not in bug table !!!
        BUG.bg_summary        AS Summary                    ,
        BUG.bg_user_80        AS MLTestEnvironment          ,
        sysdate               AS LoadDateTime               , --changed to sysdate
        BUG.bg_user_12        AS Deferred
FROM 
      tascs_ml_bac_transition_db.BUG BUG
      INNER JOIN 
      ALL_LISTS al
            ON BUG.AL_ITEM_ID = al.AL_ITEM_ID --THIS ASSUMES AL_ITEM_ID IS COMMON FIELD
      INNER JOIN 
      RELEASES REL
            ON BUG.REL_ID     = REL.REL_ID --THIS ASSUMES REL_ID IS COMMON FIELD

I'm going to take a stab at this:

SELECT 
bg_user_56 AS Project, 
bg_user_60 AS SubSystem, 
BG_USER_81 AS AssignedToUserName, 
bg_responsible AS AssignedTo,  
bg_status AS Status, 
BG_USER_15 AS BusinessFunction, 
bg_detection_date AS DetectedOnDate, 
BG_SEVERITY AS BusinessSeverity,  
bg_user_36 AS TestingSeverity, 
bg_bug_id AS DefectID, 
Bg_User_09 AS EstFixedDate,
bg_user_25 AS EstReadyForRetest,
BG_DESCIPTION AS description, 
BG_USER_03 AS DetectedInDeploymentEvent, 
rel.BG_DetectedInRelease AS DetectedInRelease,--- ??? not in bug table !!!!
BG_USER_47 AS FunctionalAreaWorkstream,
BG_USER_19 AS PlannedFixInDeploymentEvent, 
BG_USER_55 AS PlannedFixInRelease,
BG_USER_57 AS PTMTestManager, 
Bg_User_58 AS RemediatingCTOName, 
al.BG_SUBJECT AS Subject,--- ??? not in bug table !!!
bg_summary AS Summary, 
bg_user_80 AS MLTestEnvironment, 
GETDATE() AS LoadDateTime,
bg_user_12 AS Deferred 
FROM tascs_ml_bac_transition_db.BUG B, ALL_LISTS al, releases rel
WHERE al.al_item_id = b.al_item_id and rel.rel_id = b.rel_id;

It's hard to guess if this will work since I don't have a complete DDL of these tables, but this is my best guess based on what you've given. I assumed that BUG had fields with the same names as the ones you need to join on. This might not be valid, feel free to clarify.

You could also use INNER JOIN ON syntax if you prefer it.