MySQL: Two tables joined using primary key and foreign key, how to get all records even if the foreign key is not populated yet

Question

I hope to find a solution

I have a table with the primary key stock

stkid (pk), name

Second table (share)

price, quantity, stkid (fk)

I ran this query but it only showed stocks that already had a record in the shared record I want all stocks to be displayed even if there are no records in the shared record

select name,
       0,
       sum(price*quantity) / sum(quantity) as avg,
       sum(quantity) as qty
from stock,
     share
where share.stkid = stock.stkid
group by (stock.stkid)

Answer 1

You can use the LEFT JOIN statement, which will select related rows from the share table, even if there are no linked rows in the stock table.

SELECT
  name,
  0,
  sum(price*quantity) / sum(quantity) as avg,
  sum(quantity) as qty
FROM stock
LEFT JOIN share ON share.stkid = stock.stkid
GROUP BY stock.stkid