outer joins


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: outer joins

  1. #1
    nomit Guest

    outer joins



    I've searched but not seen an answer to this question so...

    I have a query

    select t1.id , t2.date , t3.value
    from tb_ids t1, tb_dates t2, tb_values t3
    where t1.id = t3.id
    and t2.date = t3.date;

    tb_ids holds all ids
    tb_dates holds all dates
    tb_dates hols values for combinations of each id at each date

    and I want to bring back rows that have null values in TB_data for both id
    and date. Not where one of them is null but when both are. Eg id is Aplha
    AND Date is 31/1/01 doesn't exist (ie Alpha AND 31/1/01 is null) but not
    the condition (Aplha OR 31/1/01 is null. )

    doing (+) on both is not allowed and outer-joining on only one condition
    doesn't work as it is an AND condition that I'm testing for

    Any suggestion greatly recieved and sorry for being dim - This is my second
    day using SQL
    Cheers



  2. #2
    Martin Guest

    Re: outer joins



    You need to be carefull about the distinction between something being null
    and something not existing.

    SELECT T1.ID, T2.DATE
    FROM TB_ID T1, TB_DATE T2
    WHERE NOT EXISTS (SELECT T3.VALUE
    FROM TB_VALUE T3
    WHERE T3.DATE = T1.DATE AND T3.ID = T1.ID)




    "nomit" <h.babraa@lse.ac.uk> wrote:
    >
    >
    >I've searched but not seen an answer to this question so...
    >
    >I have a query
    >
    >select t1.id , t2.date , t3.value
    >from tb_ids t1, tb_dates t2, tb_values t3
    >where t1.id = t3.id
    >and t2.date = t3.date;
    >
    >tb_ids holds all ids
    >tb_dates holds all dates
    >tb_dates hols values for combinations of each id at each date
    >
    >and I want to bring back rows that have null values in TB_data for both

    id
    >and date. Not where one of them is null but when both are. Eg id is Aplha
    >AND Date is 31/1/01 doesn't exist (ie Alpha AND 31/1/01 is null) but not
    >the condition (Aplha OR 31/1/01 is null. )
    >
    >doing (+) on both is not allowed and outer-joining on only one condition
    >doesn't work as it is an AND condition that I'm testing for
    >
    >Any suggestion greatly recieved and sorry for being dim - This is my second
    >day using SQL
    >Cheers
    >
    >



  3. #3
    nomit Guest

    Re: outer joins


    But T1 contains no dates
    just as T2 contains no ids
    only T3 contains both



    "Martin" <mboyle@accuris.ie> wrote:
    >
    >
    >You need to be carefull about the distinction between something being null
    >and something not existing.
    >
    >SELECT T1.ID, T2.DATE
    >FROM TB_ID T1, TB_DATE T2
    >WHERE NOT EXISTS (SELECT T3.VALUE
    > FROM TB_VALUE T3
    > WHERE T3.DATE = T1.DATE AND T3.ID = T1.ID)
    >
    >
    >
    >
    >"nomit" <h.babraa@lse.ac.uk> wrote:
    >>
    >>
    >>I've searched but not seen an answer to this question so...
    >>
    >>I have a query
    >>
    >>select t1.id , t2.date , t3.value
    >>from tb_ids t1, tb_dates t2, tb_values t3
    >>where t1.id = t3.id
    >>and t2.date = t3.date;
    >>
    >>tb_ids holds all ids
    >>tb_dates holds all dates
    >>tb_dates hols values for combinations of each id at each date
    >>
    >>and I want to bring back rows that have null values in TB_data for both

    >id
    >>and date. Not where one of them is null but when both are. Eg id is Aplha
    >>AND Date is 31/1/01 doesn't exist (ie Alpha AND 31/1/01 is null) but not
    >>the condition (Aplha OR 31/1/01 is null. )
    >>
    >>doing (+) on both is not allowed and outer-joining on only one condition
    >>doesn't work as it is an AND condition that I'm testing for
    >>
    >>Any suggestion greatly recieved and sorry for being dim - This is my second
    >>day using SQL
    >>Cheers
    >>
    >>

    >



  4. #4
    Martin Guest

    Re: outer joins



    apols - typo

    should be

    SELECT T1.ID, T2.DATE
    FROM TB_ID T1, TB_DATE T2
    WHERE NOT EXISTS (SELECT T3.VALUE
    FROM TB_VALUE T3
    WHERE T3.DATE = T2.DATE AND T3.ID = T1.ID)



    "nomit" <h.babraa@lse.ac.uk> wrote:
    >
    >But T1 contains no dates
    >just as T2 contains no ids
    >only T3 contains both
    >
    >
    >
    >"Martin" <mboyle@accuris.ie> wrote:
    >>
    >>
    >>You need to be carefull about the distinction between something being null
    >>and something not existing.
    >>
    >>SELECT T1.ID, T2.DATE
    >>FROM TB_ID T1, TB_DATE T2
    >>WHERE NOT EXISTS (SELECT T3.VALUE
    >> FROM TB_VALUE T3
    >> WHERE T3.DATE = T1.DATE AND T3.ID = T1.ID)
    >>
    >>
    >>
    >>
    >>"nomit" <h.babraa@lse.ac.uk> wrote:
    >>>
    >>>
    >>>I've searched but not seen an answer to this question so...
    >>>
    >>>I have a query
    >>>
    >>>select t1.id , t2.date , t3.value
    >>>from tb_ids t1, tb_dates t2, tb_values t3
    >>>where t1.id = t3.id
    >>>and t2.date = t3.date;
    >>>
    >>>tb_ids holds all ids
    >>>tb_dates holds all dates
    >>>tb_dates hols values for combinations of each id at each date
    >>>
    >>>and I want to bring back rows that have null values in TB_data for both

    >>id
    >>>and date. Not where one of them is null but when both are. Eg id is Aplha
    >>>AND Date is 31/1/01 doesn't exist (ie Alpha AND 31/1/01 is null) but not
    >>>the condition (Aplha OR 31/1/01 is null. )
    >>>
    >>>doing (+) on both is not allowed and outer-joining on only one condition
    >>>doesn't work as it is an AND condition that I'm testing for
    >>>
    >>>Any suggestion greatly recieved and sorry for being dim - This is my second
    >>>day using SQL
    >>>Cheers
    >>>
    >>>

    >>

    >



  5. #5
    Mike Guest

    Re: outer joins


    I wonder if something like this would work. This happens to be against one
    table -- but I don't see why you couldn't substitute multiples:

    select *
    from rpt_bstdip00000012_eod
    where 1=1
    and ( 'Lions' =nvl(obsf_type_code,'Lions')
    and 'Tigers' =nvl(obsf_program_name,'Tigers')
    and 'Bears' =nvl(pool_dscr,'Bears')
    )
    /

    "nomit" <h.babraa@lse.ac.uk> wrote:
    >
    >But T1 contains no dates
    >just as T2 contains no ids
    >only T3 contains both
    >
    >
    >
    >"Martin" <mboyle@accuris.ie> wrote:
    >>
    >>
    >>You need to be carefull about the distinction between something being null
    >>and something not existing.
    >>
    >>SELECT T1.ID, T2.DATE
    >>FROM TB_ID T1, TB_DATE T2
    >>WHERE NOT EXISTS (SELECT T3.VALUE
    >> FROM TB_VALUE T3
    >> WHERE T3.DATE = T1.DATE AND T3.ID = T1.ID)
    >>
    >>
    >>
    >>
    >>"nomit" <h.babraa@lse.ac.uk> wrote:
    >>>
    >>>
    >>>I've searched but not seen an answer to this question so...
    >>>
    >>>I have a query
    >>>
    >>>select t1.id , t2.date , t3.value
    >>>from tb_ids t1, tb_dates t2, tb_values t3
    >>>where t1.id = t3.id
    >>>and t2.date = t3.date;
    >>>
    >>>tb_ids holds all ids
    >>>tb_dates holds all dates
    >>>tb_dates hols values for combinations of each id at each date
    >>>
    >>>and I want to bring back rows that have null values in TB_data for both

    >>id
    >>>and date. Not where one of them is null but when both are. Eg id is Aplha
    >>>AND Date is 31/1/01 doesn't exist (ie Alpha AND 31/1/01 is null) but not
    >>>the condition (Aplha OR 31/1/01 is null. )
    >>>
    >>>doing (+) on both is not allowed and outer-joining on only one condition
    >>>doesn't work as it is an AND condition that I'm testing for
    >>>
    >>>Any suggestion greatly recieved and sorry for being dim - This is my second
    >>>day using SQL
    >>>Cheers
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center