-1

I'm trying to get company items using an SQL statement which uses three tables and 1 WHERE entry on a local XAMPP server. I cannot figure out why I'm receiving null entries. I have given the database details below. Thanks

SQL statement:

SELECT company_item.id, item_type, name, COUNT(item_demand.id_item), COUNT(item_sold.id_item) 
FROM company_item 
JOIN item_demand ON company_item.id = item_demand.id_item 
JOIN item_sold ON company_item.id = item_sold.id_item 
WHERE company_item.id_company="fhajdkadas";

OUTPUT:

enter image description here

TABLE: company_item: for now it only contains 1 company item and all the values have been correctly entered, I have checked.

enter image description here

TABLE: item_demand: for now this table does not have any record and I think it should not affect the SQL statement.

enter image description here

TABLE: item_sold: for now this table also does not have any record and I think it should not affect the SQL statement.

enter image description here

melpomene
  • 84,125
  • 8
  • 85
  • 148
aman
  • 307
  • 21
  • 48

1 Answers1

2

Because there is no data in your item_demand and item_sold tables, when you attempt to JOIN them to the company_item table it results in you getting no results. You need to use a LEFT JOIN instead as that will return data from the company_item table even when there is no data in either of the item_* tables.

SELECT company_item.id, item_type, name, COUNT(item_demand.id_item), COUNT(item_sold.id_item) 
FROM company_item 
LEFT JOIN item_demand ON company_item.id = item_demand.id_item 
LEFT JOIN item_sold ON company_item.id = item_sold.id_item 
WHERE company_item.id_company="fhajdkadas";
Nick
  • 138,499
  • 22
  • 57
  • 95
  • 1
    This Q&A is worth reading: https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins – Nick Apr 13 '19 at 23:46