I'm in need of writing a select statement that has to link two tables together using one column value containing a list of items separated by ',' and a column with each item listed individual, that can also filter the result using those same columns. Example:
tbl_requests
requestid | requesteditems
1234567 | laptop,monitor,mouse
1234568 | laptop,dock
1234569 | monitor,keyboard,mouse
1234570 | monitor,dock,keyboard
tbl_storage
storageid | item
123 | laptop
123 | monitor
123 | mouse
123 | keyboard
154 | laptop
154 | dock
I have no control of how the data is inserted into the request table. Regardless, I need a select statement that will return each requestid and a single storageid that contains all the requesteditems for that request. If no storagid is associated with all all requesteditems, that request is just left out. AKA:
Result Table
requestid | storageid
1234567 | 123
1234568 | 154
1234569 | 123
1234570 shouldn't be included as not storage id is associated with all requested items.
As far as I've gotten, I can link the two tables using unnest as follows:
select distinct on (r.requestid) r.request, s.storageid from tbl_requests r, unnest(string_to_array(r.requesteditems, ',')) r(items)
join tbl_storage s on s.item=r.items
This gets me close, but the result can vary as request 1234568 might return either 123 or 154 as storageid because the first item listed is laptop, which both storageid's have a record associated with laptop. Also, request 1234570 will be returned with storageid 123 because that storage id does have monitor associated with it.
Is it possible to add conditioning to the above query to ensure the only requests returned are ones with a storageid associate with every item listed in requesteditem; or am I going about this all wrong?
You can aggregate all storage items into an array, convert the requesteditems into a proper array and then join on those arrays:
with storage as (
select storageid, array_agg(item) items
from tbl_storage
group by storageid
), requests as (
select requestid, string_to_array(requesteditems,',') as items
from tbl_requests
)
select r.requestid, s.storageid
from requests r
join storage s on s.items @> r.items
The expression s.items @> r.items is true if the array if the storage items (s.items) contains all elements in the array with the requested items.