CODEWITHSUNDEEP
sqloracle-databaseoptimization
user5955972
user5955972

Reputation: 31

SQL Priority Select

I'm trying to select in Oracle with priority. Found SQL Select with Priority which is similar, but my priority's table structure is different (and I have no control over the table structures). Sample data is:

item_table:
item_id | part_id | priority_id | snapshot

      1 |       1 |           1 |       42
      1 |       2 |           1 |       42

part_table:
part_id | priority_type | value | snapshot
      1 |             P |    10 |       42
      1 |             F |    20 |       42
      2 |             P |    10 |       42
      2 |             D |    50 |       42

priority_table
priority_id | priority_1 | priority_2 | priority_3
          1 |          D |          P |          F
          2 |          P |          B |          C

The part table only has the priority type, not the ID. The item table only has the part's priority id, not the type. The snapshot is unique to each item, so I can find a specific part in part_table if I know it's part_id and snapshot from item_table.

The output I'm looking to get is similar to:

item_id | part_id | value
      1 |       1 |    10
      1 |       2 |    50

select the item id, part id, value
    where value is from row where priority_type = priority_1 where priority_id from item table = priority_id from priority_table (item and part tables joined by part_id) if such an entry for priority_type exists in part_table.
    If entry for priority_1 isn't found in part table, select value where priority_type = priority_2, etc

It's possible for a priority_1 entry to not exist for a given part / priority_id, in which case the priority_2 should be taken if exists, if not, priority_3 (there are a total of 4, if that makes things easier)

Looking to get a list of all items, all parts for said item and the 'highest in priority' value for each part. I realize the priority table could be much better created, but that's outside of my control.

So far I've come up with a nested case, similar to:

select    i.item_id,
          i.part_id,
          p.value
from      item_table i
join      part_table p
on        i.part_id = p.part_id
where     p.priority_type = (case when 
                             (select priority_1 
                             from priority_table 
                             where priority_id = 
                                 (select priority_id 
                                 from part_table 
                                 where part_id = p.part_id 
                                 and snapshot = p.snapshot)) = priority_type 
                             then priority_type
                             else...(inner case for priority_2, which has an else containing an inner case for priority_3, which has an else containing an inner case for priority_4)

I realize this is far from an optimal solution, but SQL isn't my main thing, and the structure of this particular priority table isn't how one would (should) structure it normally. I feel like I'm missing something very straightforward, but can't figure it out.

Upvotes: 2

Views: 137

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way to do it

SELECT it.item_id,
       it.part_id,
       COALESCE(p1.value, p2.value, p3.value) AS value
FROM   item_table it
       INNER JOIN priority_table pt
               ON it.priority_id = pt.priority_id
       LEFT JOIN part_table p1
              ON p1.priority_type = pt.priority_1
                 AND p1.part_id = it.part_id
       LEFT JOIN part_table p2
              ON p2.priority_type = pt.priority_2
                 AND p2.part_id = it.part_id
       LEFT JOIN part_table p3
              ON p3.priority_type = pt.priority_3
                 AND p3.part_id = it.part_id

Upvotes: 5

Related Questions