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

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 

2 Answers

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

Do not use nested selects when it is not necessary, try to use joins, and query will be more simple and will look something like this

select * from receipts join customers on receipts.customer_id = customers.customer_id join receipt_item_details1 on receipts.receipt_id = receipt_item_details1.receipt_id join brands on receipt_item_details1.brand_id = brands.brand_id 

Instead of asterisk you can define columns you want to get