SFW SQL QUERY Ambiguous field

Question

I am trying to run a query that references 3 tables and return a field from one table. I am receiving an error saying ambiguous field even though i have tried to identify columns tables. I'm not sure with the whole code yet, because i can't get past the ambiguous error to test what i have so far.

Edit: Column 'supplier_id' in field list is ambiguous is the error i am receiving

Select supplier_id, company_name, country, sales_contact_name, email
FROM SUPPLIER, COFFEE, COFFEE_SHOP
Where SUPPLIER.supplier_id = COFFEE.supplier_id 
and COFFEE.shop_id=COFFEE_SHOP.shop_id 
and COFFEE_SHOP.city = "XXX";

Answer 1

In short, the output by default is ALL columns from 3 tables. When we specify the output column, if the same column name exists in more than one input tables, we need to explicitly tell query engine in [table_alias].[column_name] format.

Depend on your DB engine, the provided query will generate a product join, which is very resource consuming and in-efficient.

You can rewrite the query as below:

SELECT 
    SUPPLIER.supplier_id, 
    company_name, 
    country, 
    sales_contact_name, 
    email
FROM SUPPLIER
JOIN COFFEE
ON SUPPLIER.supplier_id = COFFEE.supplier_id
JOIN
COFFEE_SHOP
ON
COFFEE.shop_id=COFFEE_SHOP.shop_id 
and COFFEE_SHOP.city = "XXX"

Answer 2

In addition to always writing out your joins, you should also always use aliases for your tables or views on any query complicated enough to involve a join.

SELECT s.supplier_id, company_name, country, sales_contact_name, email
FROM SUPPLIER s
INNER JOIN COFFEE c ON c.supplier_id = s.supplier_id
INNER JOIN COFFEE_SHOP cs ON cs.shop_id = c.shop_id
WHERE cs.city = 'XXX';

Also, the SQL language likes single quotes rather than double quotes for string literals, and though not all databases follow this the ANSI standard actually reserves doubles quotes for object names. This means as originally written the "XXX" literal was likey to be interpreted as the name of a table or view instead of a string value.