CODEWITHSUNDEEP
sqllinq
Phil Golding
Phil Golding

Reputation: 471

Selecting a list of available numbers between a range, joining a reserved range table

I currently have three tables:

Product

    Number | Name | Description

ProductNumberReservation

    ID | Start | End | Description

NumberRange

    Num

NumberRange is simply a sequential list of possible product numbers (Effectively 1-999999). ProductNumberReservation is a table that contains a range of Product numbers considered "Reserved" Product is bog standard list of products in the system.

I want to be able to see the status of all product IDs in a range (example 1-6 below) something like so:

| Number | Description         |
|--------|---------------------|
| 1      | Our First Product   |
| 2      |                     |
| 3      | Reservation Range 1 |
| 4      | Reservation Range 1 |
| 5      | Reservation Range 1 |
| 6      |                     |

Reservations can overlap, but I'm happy with multiple rows being returned, like so:

| Number | Description         |
|--------|---------------------|
| 1      | Our First Product   |
| 2      |                     |
| 3      | Reservation Range 1 |
| 4      | Reservation Range 1 |
| 4      | Reservation Range 2 |
| 5      | Reservation Range 1 |
| 5      | Reservation Range 2 |
| 6      |                     |

Thanks!

Upvotes: 0

Views: 34

Answers (1)

Neil
Neil

Reputation: 46

This should do what you need... (note that I've changed the field names Start and End to RangeStart and RangeEnd to avoid using the End keyword)

SELECT n.Num, COALESCE(p.Description, r.Description) as Description
FROM Numbers n
LEFT OUTER JOIN Product p
    ON n.Num = p.Number
LEFT OUTER JOIN ProductNumberReservation r
    ON n.Num >= r.RangeStart
    AND n.Num <= r.RangeEnd

Upvotes: 3

Related Questions