Consider the following dataset (may look weird but want to land my point that the strings are arbitrary):
Table A
| TicketId | StartAnimal | EndAnimal |
|---|---|---|
| 1 | Monkey | Bee |
| 1 | Lion | Buffalo |
Table B
| Animal | Sequence |
|---|---|
| Monkey | 1 |
| Zebra | 2 |
| Bee | 3 |
| Turtle | 4 |
| Lion | 5 |
| Buffalo | 6 |
Is it possible to retrieve the animals that correspond to Ticket ID 1 based on the different "ranges" in each of its rows? For example,for Ticket ID 1 the following animals should be retrieved: Monkey, Zebra, Bee, Lion, Buffalo.
As you can see the animal strings themselves have no order logic to it, but the sequence can be leveraged for it. I'm just failing to come up with how to reference it for each row in a single query.
Edit As an edge case, sometimes the EndAnimal might not even have a sequence to start with, in which case only the StartAnimal should be returned. As an example, assuming Bee is not in the sequence table, we should only get Monkey, Lion and Buffalo. Is that something SQL can handle?
Thanks!
CodePudding user response：
There are numerous ways, one such way is to inner join the tables to find the corresponding start and end sequences and then find those rows that qualify:
with s as (
select bs.Sequence s1, IsNull(be.sequence,1) s2, a.ticketId
from a left join b bs on bs.animal = a.StartAnimal
left join b be on be.Animal = a.EndAnimal
)
select b.Animal
from b
join s on b.Sequence >=s1 and b.Sequence <= s2
where s.ticketId = 1
order by b.Sequence;