Re: Selecting from joined tables (simple question)

"willy gates" <willy_gates_at_hotmail.com> wrote in message news:4344f587.0401290317.3de963b3_at_posting.google.com...
> Thanks for help with this one
>
> I have 2 tables
>
> DATA_STANDARD with columns ID and VALUE
>
> and
>
> DATA_EXTRA with column DS_ID and EXTRA_VALUE
>
> The idea is that extra information is stored in the DATA_EXTRA table
> when required.
>
> How do I select all values from DATA_STANDARD where there is either no
> entry in DATA_EXTRA or EXTRA_VALUE is zero.
>
> something like this?
>
> select ds.id from DATA_STANDARD ds, DATA_EXTRA de
> where
> ( ds.id not in ( select DS_ID from DATA_EXTRA ) )
> or
> (
> --Join
> ( ds.id = de.DS_ID )
> and
> ( de.extra_value is null )
> )
>
> Why is this wrong?
>
> Thanks

willy gates

I'm not sure whether your looking for 0 or null in de.extra_value. You may be looking for union rather than or.

This may be a solution :

select ds.id from DATA_STANDARD ds
where
( ds.id not in ( select DS_ID from DATA_EXTRA ) ) union
select ds.id from DATA_STANDARD ds, DATA_EXTRA de where
  ( ds.id = de.DS_ID )
  and
  ( de.extra_value = 0 )

--
Remove the dross to contact me directly