请求生成关联查询SQL语句：基于product_free_issue关联的三张表

Got it, let's put together the right query for your table setup.

First, let's recap the relationship: the pfi_id primary key from product_free_issue acts as the foreign key for both product_free_issue_detail (via pfd_pfi_id) and product_free_issue_audit (via pa_pfi_id). We'll use joins to tie these tables together and pull the exact fields you need.

The SQL Query #

SELECT
    pfi.pfi_id,
    pfi.pfi_lo_id,
    pfd.pfd_id,
    pfd.pfd_pfi_id,
    pfd.pfd_pr_price,
    pa.pa_id,
    pa.pa_issue_qty,
    pa.pa_missing_extra
FROM product_free_issue pfi
INNER JOIN product_free_issue_detail pfd 
    ON pfi.pfi_id = pfd.pfd_pfi_id
INNER JOIN product_free_issue_audit pa 
    ON pfi.pfi_id = pa.pa_pfi_id;

Quick Breakdown #

• We use short aliases (pfi, pfd, pa) for each table to keep the query clean and readable.
• INNER JOIN ensures we only return rows where there are matching records across all three tables. If you need to include entries from the main product_free_issue table even if there's no corresponding detail or audit data, swap INNER JOIN with LEFT JOIN instead.
• We explicitly list all the fields you requested, plus the audit-related fields since they're linked (you can remove those if you don't need them in your output).

Expected Output (Using Your Sample Data) #

This output shows all valid combinations of detail and audit records tied to your main free issue entry.

内容的提问来源于stack exchange，提问作者Rajeev Nath Verma