如何在SQL中结合WHERE子句与ORDER BY实现LEFT JOIN？业务场景说明

Got it, let's put together the SQL query you need for your program that displays ad records and transaction info. Based on your table structure and requirements, here's the query using LEFT JOIN, WHERE, and ORDER BY:

SELECT 
    contracts_tb.contract_id,
    contracts_tb.media_idref,
    contracts_tb.customer_idref
FROM 
    contracts_tb
LEFT JOIN 
    customer_tb ON contracts_tb.customer_idref = customer_tb.cus_id
LEFT JOIN 
    mediaadv_tb ON contracts_tb.media_idref = mediaadv_tb.media_id
WHERE 
    contracts_tb.STATUS = 'Active'
ORDER BY 
    contracts_tb.contract_id ASC; -- Adjust this to your preferred sort field/order

Quick breakdown of the query: #

• LEFT JOIN: We're using this to link contracts_tb with customer_tb and mediaadv_tb via their respective foreign keys. Unlike INNER JOIN, LEFT JOIN ensures you still get all active contract records even if there's a missing (unexpected) entry in the linked tables (though your foreign key constraints should prevent that, it's a safe practice).
• WHERE Clause: Filters the results to only include contracts where the STATUS is exactly "Active".
• ORDER BY: Sorts the final results—here I used contract_id ASC to order by contract ID ascending, but you can swap this out for another field (like media_idref or customer_idref) and change ASC to DESC if you want descending order.

内容的提问来源于stack exchange，提问作者Courièr Rob