Board index » delphi » SQL Join question

SQL Join question

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

 

Re:SQL Join question


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

Quote
"Ed Bernosky" <EBerno...@spsmail.com> wrote in message

news:3A2F1BA5.7BB6139@spsmail.com...
Quote
> 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

Re:SQL Join question


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

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

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

Re:SQL Join question


Your solution is much better than mine.

Quote
"Paul Lambadaris" <p...@singular.gr> wrote in message

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

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

Other Threads