Question 21434 in Mysql Question posted in
The official MySQL documentation can be found here.

how to populate the data from table which unmapped column in join – Mysql

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

  1. if PAYID is mapped with CATEGORY_ID then return the PAYID

  2. if PAYID is not mapped to CATEGORY_ID then look for BILL_ID and populate is by joining the NETWORK_CATEGORY and CATEGORY table.

  3. 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.

Tags:

2

Answers


  1. 0

    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`
    Login or Signup to reply.
  2. 0 
    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search