SQL / Subquery Question (I think)

[G_Floppy]

I have two tables...

"A" with just one column "name" and the following data:

Greg
Doug
Brian

"B" has four columns: p1, p2, s1, s2 as follows:

Greg Doug 10 20
Greg Brian 30 40
Doug Brian 50 100


In one query, I'd like to combine the two tables, grabbing A.name, (either B.s1 or B.s2 where A.name = B.p1 or B.p2) as s.

The result in this example should be a two column table like this:

Greg 10
Doug 20
Greg 30
Brian 40
Doug 50
Brian 100

Thanks in advance for any help.

[BrainDonor]

Try this:

select distinct a.name, b.s1 as s
from a, b
where a.name=b.p1
UNION
select distinct a.name, b.s2
from a, b
where a.name=b.p2

[G_Floppy]

Thanks!

Your solution got me close... I actually needed UNION ALL to get there.

[BrainDonor]

Thanks!

Your solution got me close... I actually needed UNION ALL to get there.


great! glad to help. :)

[mdjo]

Why join on A at all? Why not just:


select p1,s1 from b
union all
select p2,s2 from b


But then, I'm sure the example was contrived, maybe in real life your table A really had other data you needed or some such.