Hey there! The error you're hitting comes down to SQL's scope rules: derived tables (the subqueries wrapped in parentheses in your FROM clause) are evaluated independently. That means the second subquery (for member_card) can't reference columns from the first derived table m—they're not in scope when that subquery runs.

Let's walk through two common fixes depending on your database system:

Option 1: Use LATERAL JOIN (Supported in PostgreSQL, MySQL 8.0.14+, Oracle 12c+) #

LATERAL JOIN lets subqueries access columns from tables that appear earlier in the FROM clause. This is perfect for your case, since it lets the member_card subquery reference m.id directly:

SELECT c.id, m.member_no, mc.card_no 
FROM customer AS c 
LEFT JOIN ( 
    -- Note: I changed `m1.cust_id = 123` to `m1.cust_id = c.id` here—assuming you want the latest membership per customer, not just for customer 123. If you *do* only need customer 123, you can put 123 back.
    SELECT * 
    FROM membership 
    WHERE creation_date = (SELECT MAX(creation_date) FROM membership AS m1 WHERE m1.cust_id = c.id)
) AS m ON m.cust_id = c.id 
LEFT JOIN LATERAL ( 
    SELECT * 
    FROM member_card 
    WHERE emboss_date = (SELECT MAX(emboss_date) FROM member_card AS mc1 WHERE mc1.membership_id = m.id)
) AS mc ON mc.membership_id = m.id 
WHERE ... -- Your existing WHERE conditions

Option 2: Use Window Functions (Works in Most Modern Databases) #

If your database doesn't support LATERAL JOIN, window functions like ROW_NUMBER() let you pre-mark the latest records for each group, then filter those in the JOIN:

WITH latest_membership AS (
    SELECT *,
           -- Assign a rank to each membership per customer, latest first
           ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY creation_date DESC) AS rn
    FROM membership
),
latest_member_card AS (
    SELECT *,
           -- Assign a rank to each card per membership, latest first
           ROW_NUMBER() OVER (PARTITION BY membership_id ORDER BY emboss_date DESC) AS rn
    FROM member_card
)
SELECT c.id, m.member_no, mc.card_no 
FROM customer AS c 
LEFT JOIN latest_membership AS m ON m.cust_id = c.id AND m.rn = 1 -- Only keep the latest membership per customer
LEFT JOIN latest_member_card AS mc ON mc.membership_id = m.id AND mc.rn = 1 -- Only keep the latest card per membership
WHERE ... -- Your existing WHERE conditions

Both approaches eliminate the scope issue by either letting the subquery reference m.id directly (LATERAL JOIN) or precomputing the latest records before joining (window functions). Feel free to tweak based on your specific database and exact needs!

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