MySQL Join three tables and display 0 if null
I have three tables:
person_table
ID | name | floor
1 | Joe | the man
2 | Jane | woman
3 | Janet | woman
4 | Jay | male, etc.
product_table
ID | name
1 | magazine
2 | book
3 | article
4 | novel
etc.
** person_product
person_id | product_id | quantity
1 | 1 | 1
1 | 3 | 3
2 | 3 | 1
4 | 4 | 2
etc.
I tried to make a query that would return a table like this: person_id | person_name | product_name | quantity but I cannot make it so that if it can be said that John has no books, he should display (johns id) John | book | 0 instead of just skipping that line. Where am I wrong? here is what I managed to come up with:
SELECT p.*, f.name, l.quantity
FROM person_product AS l
INNER JOIN people_table AS p ON l.person_id=p.id
INNER JOIN product_table AS f ON l.product_id=f.id
ORDER BY id`
source to share
It seems that you are creating a report of all people, for all products with the corresponding quantity; on a large dataset, this can take a while since you are not directly joining the product for anything other than quantity:
SELECT
p.id,
p.name,
p.gender,
f.name,
IFNULL(l.quantity,0) AS quantity
FROM person_table AS p
JOIN product_table AS f
LEFT JOIN person_product AS l
ON l.person_id = p.id
AND l.product_id = f.id
ORDER BY p.id, f.name
Result:
Is it more or less than what you are after?
source to share
you need to start with people_table than with a left join, you need to bring other table data.
since you need the value 0 if the value is null than you can use the IFNULL function
SELECT p.*, f.name, IFNULL(l.quantity,0)
FROM people_table AS p
LEFT JOIN person_product AS l ON l.person_id=p.id
LEFT JOIN product_table AS f ON l.product_id=f.id
ORDER BY p.id
source to share