return data from two tables

Hi,
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:

> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:

>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql