I'm trying to get all the records in T1 but only the values from
T2.c when T2.b = 2 otherwise I want that field null. What am I
doing wrong?  TIA

T1
a
1
2
3
4

T2
a b c
1 1 X
3 2 X
4 3 X

select T1.a, T2.c
from T1 left outer join T1 on (T1.a = T2.a)
where (T2.b = 2)

What I want returned is
a c
1 null
2 null
3 X
4 null

Ed Bernosky

Your problem is that the join in your query gets processed before the where
clause resticts the records in T2 that are joined. You need to restrict the
set of records in T2 first, then join them to T1.

I don't know what server you're using, but this is how you'd use a derived
table to get the answer in SQL Anywhere:

select T1.a, Tx.c
from T1 left outer join
    (select  a, c from T2 where b = 2) Tx
    on T1.a = Tx.a

If your server doesn't support derived tables in joins, you can use a view
the same way.

  Richard

news:3A2F1BA5.7BB6139@spsmail.com...

In article <3A2F1BA5.7BB6...@spsmail.com>, EBerno...@spsmail.com says...

Try this
select T1.a, T2.c
from T1 left outer join T1 on (T1.a = T2.a) and (T2.b = 2)

-------------------------------
Paul Lambadaris
Singular Computer Applications
mailto : p...@singular.gr
www    : http://www.singular.gr

Your solution is much better than mine.

news:MPG.14996949940a2b8a9896da@forums.inprise.com...