基于property_id查询关联交易数据的MySQL多表嵌套关联问题

First, let's break down the two ways a transaction can be tied to a specific property_id:

• Directly through the transaction_properties table (where the transaction is explicitly linked to the property)
• Indirectly via the transaction_inventory → inventories chain (where the transaction uses an inventory item that belongs to the target property)

We need to gather all unique transaction_ids from both paths, then pull the corresponding transaction details from the transactions table.

Option 1: Subquery with UNION for Conciseness #

SELECT t.*
FROM transactions t
WHERE t.id IN (
    -- Get transactions directly associated with the property
    SELECT transaction_id
    FROM transaction_properties
    WHERE property_id = :target_property_id
    
    UNION
    
    -- Get transactions linked via inventory items belonging to the property
    SELECT ti.transaction_id
    FROM transaction_inventory ti
    JOIN inventories i ON ti.inventory_id = i.id
    WHERE i.property_id = :target_property_id
);

Option 2: CTE for Better Readability #

WITH eligible_transactions AS (
    SELECT transaction_id
    FROM transaction_properties
    WHERE property_id = :target_property_id
    
    UNION
    
    SELECT ti.transaction_id
    FROM transaction_inventory ti
    JOIN inventories i ON ti.inventory_id = i.id
    WHERE i.property_id = :target_property_id
)
SELECT t.*
FROM transactions t
JOIN eligible_transactions et ON t.id = et.transaction_id;

Important Details: #

• We use UNION (not UNION ALL) to automatically remove duplicate transaction_ids—this handles cases where a transaction is both directly linked to the property and uses an inventory item from the same property.
• Replace :target_property_id with your actual property ID value (adjust the placeholder syntax if needed for your SQL dialect, like ? for MySQL or $1 for PostgreSQL).
• Both queries return full transaction details (id, name, qty) for every matching transaction.

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