Query multiple tables including rows with null foreign keys MySQL
foreign-keys, multiple-tables, mysql, null
Solution
You need to use Left Join then.
SELECT category.id_product, manufacturer.name, supplier.name, P.ID_PRODUCT
FROM product
LEFT JOIN category C
ON C.id_category = P.id_category
LEFT JOIN manufacturer M
ON M.id_manufacturer = P.id_manufacturer
LEFT JOIN supplier S
ON S.id_supplier = P.id_supplier;
These will give all the rows of product table even if it has no corresponding row from other tables.
Problem
It's my first time with mysql and I need some help. I couldn't find any solution to this probably because it's too easy or I'm too noob to look it up properly yet. I want to make a query from multiple tables from my test database. Let's say tables are: category (*id_category, *id_product) product (*id_product, id_category, id_supplier, id_manufacturer) manufacturer (*id_manufacturer, name) supplier (*id_supplier, name) The columns with * are integer primary key not null with auto_increment(starting from 1). The other id columns are just integer foreign keys(default null). I need a query also including rows with null id_manufacturer and/or id_supplier on the product table. Those empty fields in the product table return value '0', so when I make a query like ``` SELECT category.id_product, manufacturer.name, supplier.name FROM category, product, manufacturer, supplier WHERE category.id_category = product.id_category AND manufacturer.id_manufacturer = product.id_manufacturer AND supplier.id_supplier = product.id_supplier; ``` None of the rows with null values(0)are shown. I'd like you to point me in the most clean and efficient way to do it without changing table properties. Any help would be very much appreciated.