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!

Right Join Problems Newbie

Discussion in 'SQL PL/SQL' started by tonyb, Feb 3, 2009.

  1. tonyb

    tonyb Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All

    Firstly thank you for reading my post.

    I am relatively new to Oracle having mainly used it only for simple recording of data and limited cross referencing tables via views. I am using Oracle Express 10g.

    I have two tanles within a databse that i need to query.

    TBLBROKER and TBLCONTACT

    I need to be able to show all the BROKERS in TBLBROKER and ALL the records in TBLCONTACT between two set dates but also show which brokers have not been contacted between the dates specified.

    If I use this SQL then it shows what I require, however I am using ALL the Data in TBLCONTACT
    >>>
    Code (Text):

    select   "TBLBROKER"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."FIRSTNAME" as "FIRSTNAME",
                 "TBLBROKER"."LASTNAME" as "LASTNAME",
                 "TBLBROKER"."COMPANY" as "COMPANY",
                 "TBLBROKER"."ADDRESS1" as "ADDRESS1",
                 "TBLBROKER"."ADDRESS2" as "ADDRESS2",
                 "TBLBROKER"."TOWN" as "TOWN",
                 "TBLBROKER"."POSTCODE" as "POSTCODE",
                 "TBLBROKER"."TEL" as "TEL",
                 "TBLBROKER"."MOBILE" as "MOBILE",
                 "TBLBROKER"."EMAIL" as "EMAIL",
                 "TBLBROKER"."ACTIVE" as "ACTIVE",
                 "TBLCONTACT"."CONTACTNARRATIVE" as "CONTACTNARRATIVE",
                 "TBLCONTACT"."CONTACTDATE" as "CONTACTDATE",
                 "TBLCONTACT"."ADDEDBY" as "ADDEDBY",
                 "TBLCONTACT"."MEETTYPE" as "MEETTYPE",
                 "TBLCONTACT"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."IDSELLER" as "IDSELLER"
     from     "TBLCONTACT" "TBLCONTACT",
                 "TBLBROKER" "TBLBROKER"
     where   "TBLBROKER"."IDBROKER"="TBLCONTACT"."IDBROKER"(+)

       and    "TBLBROKER"."IDSELLER"  = 26
    order by TBLCONTACT.CONTACTDATE DESC
     
    This shows me all the brokers in the TBLBROKER and ALL the records from TBLCONTACT and ALSO shows the "EMPTY" Rows from TBLCONTACT i.e. the brokers that have not been contacted.


    However when I add the following line :
    Code (Text):

    select   "TBLBROKER"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."FIRSTNAME" as "FIRSTNAME",
                 "TBLBROKER"."LASTNAME" as "LASTNAME",
                 "TBLBROKER"."COMPANY" as "COMPANY",
                 "TBLBROKER"."ADDRESS1" as "ADDRESS1",
                 "TBLBROKER"."ADDRESS2" as "ADDRESS2",
                 "TBLBROKER"."TOWN" as "TOWN",
                 "TBLBROKER"."POSTCODE" as "POSTCODE",
                 "TBLBROKER"."TEL" as "TEL",
                 "TBLBROKER"."MOBILE" as "MOBILE",
                 "TBLBROKER"."EMAIL" as "EMAIL",
                 "TBLBROKER"."ACTIVE" as "ACTIVE",
                 "TBLCONTACT"."CONTACTNARRATIVE" as "CONTACTNARRATIVE",
                 "TBLCONTACT"."CONTACTDATE" as "CONTACTDATE",
                 "TBLCONTACT"."ADDEDBY" as "ADDEDBY",
                 "TBLCONTACT"."MEETTYPE" as "MEETTYPE",
                 "TBLCONTACT"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."IDSELLER" as "IDSELLER"
     from     "TBLCONTACT" "TBLCONTACT",
                 "TBLBROKER" "TBLBROKER"
     where   "TBLBROKER"."IDBROKER"="TBLCONTACT"."IDBROKER"(+)
    [COLOR="Purple"]  and      "TBLCONTACT"."CONTACTDATE" between to_date('01/JAN/2009') and to_date('31/JAN/2009') [/COLOR]
       and    "TBLBROKER"."IDSELLER"  = 26
    order by TBLCONTACT.CONTACTDATE DESC
     
    Then it limits the records returned to the number of records in TBLCONTACT.

    How do I go about referencing the table between the dates and also return the 'EMPTY. rows, i.e. the brokers that have not been contacted.

    I hope this is clear enough for you to follow.

    Many Thanks

    Tony
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Welcome to Oracle-club .

    Please provide the the test case from next time to reproduce the issue . Test case include Create table script, sample insert script , Query you have tried , result you got and the expected result

    If I understood the problem ,
    becoz of newly added clause , its not returning "EMPTY" records from TBLCONTACT.

    You can use another OR condition as below.

    Code (Text):

    select   "TBLBROKER"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."FIRSTNAME" as "FIRSTNAME",
                 "TBLBROKER"."LASTNAME" as "LASTNAME",
                 "TBLBROKER"."COMPANY" as "COMPANY",
                 "TBLBROKER"."ADDRESS1" as "ADDRESS1",
                 "TBLBROKER"."ADDRESS2" as "ADDRESS2",
                 "TBLBROKER"."TOWN" as "TOWN",
                 "TBLBROKER"."POSTCODE" as "POSTCODE",
                 "TBLBROKER"."TEL" as "TEL",
                 "TBLBROKER"."MOBILE" as "MOBILE",
                 "TBLBROKER"."EMAIL" as "EMAIL",
                 "TBLBROKER"."ACTIVE" as "ACTIVE",
                 "TBLCONTACT"."CONTACTNARRATIVE" as "CONTACTNARRATIVE",
                 "TBLCONTACT"."CONTACTDATE" as "CONTACTDATE",
                 "TBLCONTACT"."ADDEDBY" as "ADDEDBY",
                 "TBLCONTACT"."MEETTYPE" as "MEETTYPE",
                 "TBLCONTACT"."IDBROKER" as "IDBROKER",
                 "TBLBROKER"."IDSELLER" as "IDSELLER"
     from     "TBLCONTACT" "TBLCONTACT",
                 "TBLBROKER" "TBLBROKER"
     where   "TBLBROKER"."IDBROKER"="TBLCONTACT"."IDBROKER"(+)
      and     ( "TBLCONTACT"."CONTACTDATE" between to_date('01/JAN/2009','DD/MON/YYYY') and to_date('31/JAN/2009','DD/MON/YYYY')  
       [COLOR=purple]OR    "TBLCONTACT"."CONTACTDATE" IS NULL [/COLOR]  )
       and    "TBLBROKER"."IDSELLER"  = 26
    order by TBLCONTACT.CONTACTDATE DESC
     
    Also it is advised to give exact format to the TO_DATE function as given in above query.
     
  3. tonyb

    tonyb Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Firstly, many thanks Rajavu for the reply, it works well.
    Secondly sorry for the delay in replying, but I was working on something else but now back on this.

    I need to query the view that I have created with Rajavu's help to return just the "EMPTY" rows.

    I am calling the view from a web page and passing the paramaters for the dates and IDSELLER. This works well but how do I go about doing the second query?

    I seem to remember from some old SQL Server code that I used to Create a "Temp Table", but I do not know how to do the same in ORACLE.

    Also as this will be accessed from a webpage how can I guarentee that the "Temp Table" that is processed by the second query will be the one that has just been create by the current user and not a subsequent request to run the same code?

    TIA

    Tony
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    can't you just do this

    Code (Text):

    select * from tony_view
    where CONTACTDATE is null;
     
    Second query? which second query? the one that rajavu wrote? please be more clear
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Creating Temporary table and processing is SQL Server's way of doing it. Oracle wont recommend it.

    Still I am not sure why can't you process the second query against the INPUT date range and IDSELLER. Even you can create a view and query from there.