outer joins

[nomit]

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

[Martin]

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
>
>

[nomit]

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
>>
>>
>

[Martin]

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
>>>
>>>
>>
>

[Mike]

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
>>>
>>>
>>
>