Satch3000 Satch3000 - 5 months ago 4x
SQL Question

Join but return ALL records from Table

I have the following SQL query:

SELECT * FROM `table1` INNER JOIN `table2` ON table1.messageid=table2.messageid WHERE `venue_active` = 1

The above works fine but it only returns fields where both tables have a messageid field.

My problem is that I need it to return ALL fields from Table1 reguardless if it has a messageid match in table2 or not.

So, in other words I need ALL records to be returned from Table1 and all records from Table2 where there's a messageid that matches both.

How can I do this?


Use a LEFT JOIN rather

FROM    `table1` LEFT JOIN 
        `table2` ON table1.messageid=table2.messageid 
WHERE   `venue_active` = 1

That said, it will only work if venue_active is also part of table1, and not table2.

Have a look at the different scenarios