3

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.

Guillem
  • 41
  • 1
  • 5

1 Answers1

5

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.

Santhosh
  • 1,771
  • 1
  • 15
  • 25