How to avoid the "ambiguous" error message when using joins to create several tables

Question

I'm trying to use the following code to create a list of customers and their brands that they buy. The brands table has the brand name and customer_id is in the customers table. To link them I have to get the brand_id and receipt_id linked together via the receipts table (connects to customers table) and receipt_item_details1 table (connects to brands table).

So, receipt_item_details1 table (has brand_id column to then connect to brands table) and new table customer_receipts (created by first inner most subquery) are trying it to be linked by receipt_id. I'd like to show the customer_id column when I build my table joining these two table (an original: receipt_item_details1 joined to a new table: customer_receipts).

ISSUE: I keep getting the following error. how do Infix it and also list the brands?

"column reference "customer_id" is ambiguous
LINE 3: ...pts.receipt_id, receipt_item_details1.receipt_id, customer_r.."

SELECT customer_brandids.brand_id, brands.brand_id, customer_brandids.customer_id, brands.brand_name
    FROM 
        (SELECT customer_receipts.receipt_id, receipt_item_details1.receipt_id, customer_receipts.customer_id, receipt_item_details1.brand_id
        FROM
            (SELECT receipts.customer_id, customers.customer_id, receipts.receipt_id
            FROM receipts
            INNER JOIN customers
            ON receipts.customer_id = customers.customer_id) AS customer_receipts
        INNER JOIN receipt_item_details1
        ON customer_receipts.receipt_id = receipt_item_details1.receipt_id) AS customer_brandids
    INNER JOIN brands
    ON customer_brandids.brand_id = brands.brand_id

Answer 1

Your inner subselect

 (SELECT receipts.customer_id, customers.customer_id

generates a result with two columns named customer_id. So your next higher subselect cannot differ between both columns if you reference customer_id

You should give one or both an alias:

 (SELECT receipts.customer_id as r_customer_id, 
      customers.customer_id as c_customer_id

Then your next higher query can call

 SELECT customer_receipts.c_customer_id...

So first step of solving the problem:

SELECT 
    customer_brandids.brand_id,                       
    brands.brand_id, 
    customer_brandids.c_customer_id,                    --> reference alias
    brands.brand_name
FROM 
    (SELECT 
         customer_receipts.receipt_id as c_receipt_id,  --> same problem
         receipt_item_details1.receipt_id as r_receipt_id,
         customer_receipts.c_customer_id,               --> reference alias
         receipt_item_details1.brand_id
    FROM
        (SELECT 
             receipts.customer_id as r_customer_id,     --> here was the problem
             customers.customer_id as c_customer_id, 
             receipts.receipt_id
        FROM receipts
        INNER JOIN customers
        ON receipts.customer_id = customers.customer_id) AS customer_receipts
    INNER JOIN receipt_item_details1
    ON customer_receipts.receipt_id = receipt_item_details1.receipt_id) AS customer_brandids
INNER JOIN brands
ON customer_brandids.brand_id = brands.brand_id

---

Addionally:

1. You don't need to take both columns (e.g. of receipt_id) because of the INNER JOIN it is ensured that both columns have the same value
2. You can use aliases to shorten your query.
3. You don't need to create a subquery for each join. Just join.

All in all, this should do the same:

SELECT b.brand_id, c.customer_id, b.brand_name 
FROM receipts r
INNER JOIN customers c ON r.customer_id = c.customer_id
INNER JOIN receipt_item_details1 rid ON r.receipt_id = rid.receipt_id
INNER JOIN brands b ON b.brand_id = rid.receipt_id

demo: db<>fiddle