user5955972 user5955972 - 7 months ago 26
SQL Question

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.

Answer

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 
Comments