how to populate the data from table which unmapped column in join-CodePudding

I am having below tables and want to populate the PAYID from the PAY table by looking in to CATEGORY_ID for below use case

if PAYID is mapped with CATEGORY_ID then return the PAYID
if PAYID is not mapped to CATEGORY_ID then look for BILL_ID and populate is by joining the NETWORK_CATEGORY and CATEGORY table.
If PAYID is not mapped to CATEGORY_ID and even BILL_ID is not associated with NETWORK_CATEGORY_ID then return the CATEGORY_ID as 3 (Uncategorized) for PAY.

Below is the my tables,

PAY Table

PAYID   BILL_ID  CATEGORY_ID
101       1        1
102       2 
103       3

BILL Table

BILL_ID      NAME    NETWORK_CATEGORY_ID
1            ABC          42
2            XYZ    
3            DSC          23

NETWORK_CATEGORY Table

NETWORK_CATEGORY_ID  NAME    CATEGORY_ID
42                   Electric/gas   1
23                   ISP    2

CATEGORIES

CATEGORY_ID  NAME
1            Utilities
2            Telecom
3            Uncategorized

And i have written below queries ,

select payid(
        select payid from PAY p
        where p.CATEGORY_ID in (:categoryIds)
    UNION
        select payid from PAY p 
            join BILLS b on p.BILL_ID = b.BILL_ID 
        where NETWORK_CATEGORY_ID in 
            (
                select NETWORK_CATEGORY_ID 
                from NETWORK_CATEGORY nc 
                    join CATEGORIES c on nc.CATEGORY_ID = c.CATEGORY_ID 
                where c.CATEGORY_ID in (:categoryIds)
            )
            and CATEGORY_ID is null 
        )

My expectation is that if i passed the categoryIds as 3 or (1,2,3 )then it should return the PAYID as 102 and 101,102,103 respectively. How can i do that. Or i can say that how can achieve this task,

If PAYID is not mapped to CATEGORY_ID and even BILL_ID is not associated with NETWORK_CATEGORY_ID then return the CATEGORY_ID as 3 (Uncategorized) for PAY.

By using three union i can achieve that but that not looks an efficient solution.

CodePudding user response：

Messy but 1) first subquery (x) to cater for no entries, 2) first part of union to check category id, 3) UNION to deal with situation where bill_id and category_id are the same, 4) second part of union to find pay via bill, 5) coalesce to cater for no entries.

select coalesce(payid,3) payid,s.`bill/category id` 
from
(select 2 `bill/category id` ) x
left join 
(select payid ,category_id as `bill/category id` from pay where category_id = 2
union
select payid ,bill.bill_id
from bill 
join network_category nc on bill.network_category_id = nc.network_category_id
join pay  on pay.bill_id = nc.category_id
where bill.bill_id = 2
      and not exists (select payid from pay where category_id = 2)
) s on s.`bill/category id` = x.`bill/category id`

CodePudding user response：

SELECT p.PAYID
FROM PAY p
WHERE p.CATEGORY_ID IN (2,3)

UNION ALL

SELECT p.PAYID
FROM PAY p
JOIN BILL b
    ON p.CATEGORY_ID IS NULL AND p.BILL_ID = b.BILL_ID
JOIN NETWORK_CATEGORY nc
    ON b.NETWORK_CATEGORY_ID = nc.NETWORK_CATEGORY_ID
WHERE nc.CATEGORY_ID IN (2,3)

UNION ALL

SELECT p.PAYID
FROM PAY p
LEFT JOIN BILL b
    ON p.CATEGORY_ID IS NULL AND p.BILL_ID = b.BILL_ID
LEFT JOIN NETWORK_CATEGORY nc
    ON b.NETWORK_CATEGORY_ID = nc.NETWORK_CATEGORY_ID
WHERE (3 IN (2,3) AND p.CATEGORY_ID IS NULL AND nc.CATEGORY_ID IS NULL)

Depending on the size of the dataset and the other criteria being applied to the PAY table, this may perform OK -

SELECT p.PAYID
FROM PAY p
LEFT JOIN BILL b
    ON p.CATEGORY_ID IS NULL AND p.BILL_ID = b.BILL_ID
LEFT JOIN NETWORK_CATEGORY nc
    ON b.NETWORK_CATEGORY_ID = nc.NETWORK_CATEGORY_ID
WHERE p.CATEGORY_ID IN (2,3)
  OR nc.CATEGORY_ID IN (2,3)
  OR (3 IN (2,3) AND p.CATEGORY_ID IS NULL AND nc.CATEGORY_ID IS NULL)