如何调整关联core_loan多表的SQL查询，拆分结果为独立行并适配列数差异

Got it, let's fix this query for you! The problem with your original code is that joining both the specific materials and generic items tables in one go creates a Cartesian product between the two sets of related records—this is why you're seeing duplicate rows for specific material IDs when a loan is linked to multiple generic items.

To get each material type as an independent row (with the unused ID field set to NULL), we'll use UNION ALL to combine two targeted queries: one for specific material instances, and another for generic items. Here's the adjusted query:

SELECT 
    l.id, 
    l.status, 
    ls.specificmaterialinstance_id, 
    NULL AS material_id
FROM "main"."core_loan" as l
LEFT JOIN "main"."core_loan_specific_materials" as ls 
    ON ls.loan_id = l.id
WHERE l.due_date < date('now','-1 day')
AND ls.specificmaterialinstance_id IS NOT NULL -- Exclude loans with no specific materials

UNION ALL

SELECT 
    l.id, 
    l.status, 
    NULL AS specificmaterialinstance_id, 
    lg.material_id
FROM "main"."core_loan" as l
LEFT JOIN "main"."core_loangenericitem" as lg 
    ON lg.loan_id = l.id
WHERE l.due_date < date('now','-1 day')
AND lg.material_id IS NOT NULL -- Exclude loans with no generic materials

-- Optional: Add this block to include loans with no linked materials at all
UNION ALL

SELECT 
    l.id, 
    l.status, 
    NULL AS specificmaterialinstance_id, 
    NULL AS material_id
FROM "main"."core_loan" as l
WHERE l.due_date < date('now','-1 day')
AND NOT EXISTS (SELECT 1 FROM "main"."core_loan_specific_materials" WHERE loan_id = l.id)
AND NOT EXISTS (SELECT 1 FROM "main"."core_loangenericitem" WHERE loan_id = l.id);

Quick breakdown of the changes: #

• First query: Pulls loans linked to specific material instances, explicitly sets material_id to NULL since these rows don't relate to generic items. The filter removes empty specific material rows (remove that line if you want to keep loans with no specific materials here).
• Second query: Fetches loans linked to generic items, sets specificmaterialinstance_id to NULL for consistency. The filter here removes rows with no generic material attached.
• Optional third block: Adds loans that have no linked materials whatsoever, with both ID fields set to NULL. Delete this section if you don't need these records.

We use UNION ALL instead of UNION to avoid accidentally removing valid duplicate rows (like if a loan has two identical generic items linked) and to keep the query performant.

内容的提问来源于stack exchange，提问作者user5329403