Problem with joining tables

[Benny]

Hello Experts,

Assume I have two tables: tA and tB

tA:
tA_ID, tB_ID, Description
1 100 A
2 200 B
3 300 C

tB:
tB_ID, Description
100 W
200 X
300 Y
400 Z

Result I want is everything in table tB with the description from its
related record in table tA if applicable. So the result will be like
this:

tB_ID, tA_ID, Description (from tA)
100 1 A
200 2 B
300 3 C
400 NULL NULL


How can I achieve with MS SQL 2000?


Thanks in advanced,

Benny

 

[Miha Markic [MVP C#]]

Hi Benny,

Did you consider using JOIN clause in select statement?

 

[Guest]

select tb.tb_id,ta.ta_id,ta.description
from tableA ta right join tableB tb on ta.ta_id = tb.tb_id

 

[Benny]

Hello Ibrahim ,

Thanks for your help. What you showed me works fine on the example,

select tb.tb_id,ta.ta_id,ta.description
from tableA ta right join tableB tb on ta.tb_id = tb.tb_id

However, in tabelA "tA" now there is a extra field named "status" that
is a bit (0 or 1), so tableA will become like this:

tA_ID, tB_ID, Description, Status
1 100 A 0
2 200 B 1
3 300 C 0

I want exactually what I want before with a new condition: where
tA.Status = 0.

select tb.tb_id, ta.ta_id, ta.description
from ta right join tb on ta.tb_id = tb.tb_id
where ta.status = 0

which returns:
tb_ID, ta_ID, Description
100 1 a
300 3 c

But result i wanted is:
tb_ID, ta_ID, Description
100 1 a
300 3 c
400 null null


How can i solve this?


Cheers,

Benny