Join repeats rows too many times

List the employees (first and last name in same column), that work in retail or food and drink. Include the building they work in and shop type.

This is what I have. Whenever I run it everything repeats so there’s over a hundred rows now. Can anybody help me?

EDIT

SELECT CONCAT(first_name, ' ',last_name) AS "Employee", shop_type AS "Shop Type", Building_Assigned AS "Building Name"
FROM employees
FULL OUTER JOIN Shops 
ON employees.first_name = Shops.shop_type 
FULL OUTER JOIN buildings
ON employees.last_name = buildings.Building_Assigned
WHERE Shops.shop_type is NOT NULL OR employees.first_name IS NOT NULL OR employees.last_name IS NOT NULL OR buildings.Building_Assigned IS NOT NULL

I’ve tried various joins for this to make this work and give me the output I desire but whenever I do it, for example, FULL OUTER Join returns a Shop Type and Building Name as NULL and Inner Join and Join only return the names of the columns. Everything else just returns as NULL. Is there any way anyone can help me with problem?

Answer

You have to use JOIN syntax for the required output.

Standard Example

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name
INNER JOIN table3
on table2.column_name2 = table3.column_name2;

Try to join your tables as the above, you will get the output.

Advertisement

Answer