Need help with join, keep getting Unknown column 'a.shop_id' in 'field list'

Question

Sorry new to this: This is my objective: Develop SQL code to create a query, the query should join together three different tables and include attributes from all three tables in its output.

This is what i have:

SELECT  a.shop_id, b.employee_id, first_name, last_name, hire_date,
        coffee_id, coffee_name, d.supplier_id, company_name
    FROM  COFFEE_SHOP
    JOIN  EMPLOYEE  ON b.employee_id = s.employee_id
    JOIN  COFFEE  ON a.shop_id = s.shop_id
    JOIN  SUPPLIER  ON d.supplier = q.supplier

Please help and if i need to provide more info. let me know

Answer 1

See all of those single letter prefixes followed by a dot and then your column names?...for example the a in a.shop_id. Those are called table aliases and are a way to give nicknames for your tables in a query. But to do that, you must first define them in your FROM and JOIN clauses directly following the table name you want to alias, such as FROM COFFEE_SHOP a.

Since your query doesn't define any of the aliases it's currently using, MySQL doesn't know which tables you're referencing when you call out column names by those aliases. So you either have to add them to each table you're utilizing such as my example above, or remove them from your columns, wherever you're referencing them.

Though it's generally best practice to use aliases (or reference the table names themselves) so that when you reference a column (especially in the SELECT list) it's explicit. If you didn't use aliases (or the full table names) and you tried to reference a column that existed with the same name in multiple tables, you'd receive a different error since it's unclear which instance of the column you really wanted.

By the way, a standard naming convention people use is the single letter abbreviation of the words in the table name. For example, COFFEE_SHOP could be aliased as CS and EMPLOYEE aliased as E.