Using value from one field to obtain additional returned rows in query 1

[Darrel77]

Please pardon the long subject, but I have no idea how to explain this issue. Has anyone out there ever tried this one...

I have a simple query that I need to complicate. This hopefully can be done. Take a look at the below sample data...

select * from TABLE_A where nbr = '1234';

NBR REC TYPE DATA NBR_OLD
1234 3 ECW TAZZ
1234 4 WCW BOOKER
1234 5 WWE T 4
1234 6 WCW TORRIE
1234 7 WWE WILSON 6
1234 8 WCW EDDIE
1234 9 WWE GUERRERO 8
1234 10 WCW CHRIS
1234 11 WWE BENOIT 10
1234 12 ECW RVD
1234 13 ECW RHYNO

My primary query is....
select * from TABLE_A where nbr = '1234' and type = 'WWE';

NBR REC TYPE DATA NBR_OLD
1234 5 WWE T 4
1234 7 WWE WILSON 6
1234 9 WWE GUERRERO 8
1234 11 WWE BENOIT 10

...now, I need to return the rows above AND the corresponding data where the REC field's value is like the value in the NBR_OLD field. So in a nutshell, my eventual output would be....

NBR REC TYPE DATA NBR_OLD
1234 4 WCW BOOKER
1234 5 WWE T 4
1234 6 WCW TORRIE
1234 7 WWE WILSON 6
1234 8 WCW EDDIE
1234 9 WWE GUERRERO 8
1234 10 WCW CHRIS
1234 11 WWE BENOIT 10

Fill in the blanks....
select * from TABLE_A where nbr = '1234' and type in ('WCW','WWE')
and ____________ ;

If I can't use the value in the NBR_OLD field to get my extra rows, is there anyway to modify my primary query to ALSO return the data from the rows DIRECTLY above it?

Any assistance would be greatly appreciated.

Thanks,
Darrel

 

[r937]

[tt]select nbr, rec, type, data, nbr_old
from table_a
where nbr = '1234'
and type = 'WWE'
union all
select old.nbr, old.rec, old.type
, old.data, old.nbr_old
from table_a wwe
inner
join table_a old
on wwe.nbr = old.nbr
and wwe.nbr_old = old.rec
where wwe.nbr = '1234'
and wwe.type = 'WWE'
order
by 1,2[/tt]

rudy

http://r937.com/

SQL Consulting

 

[Darrel77]

Thanks.....

I had this INNER JOIN code setup but somehow switched my final two aliases.

- Darrel