How to perform left join so every record from left table will be fetched irrespective of where condition on right table

Question

Basically I am trying to fetch details of pages stored in pages table in database.

I want to fetch all records from page table and the records having user_id 16 from pages_commission table.

The query I tried

SELECT pages.*, IFNULL(pc.commission, 0) AS commission FROM `pages` LEFT JOIN `pages_commission` AS pc ON pages.page_id=pc.page_id WHERE pc.user_id=16

But I am gettings zero rows.

Table structure:

1. pages table

2. pages_commission table

Database: MySql

Answer 1

The WHERE is turning the join into an inner join. Move the constant condition to the ON clause:

SELECT p.*, COALESCE(pc.commission, 0) AS commission
FROM `pages` p LEFT JOIN
     `pages_commission`pc
     ON p.page_id = pc.page_id AND pc.user_id = 16;