Query in multiple tables with MYSQL

Question

I have 2 tables.

Table_1 has product 3 rows : ID, Quantity and Price. Table_2 has 2 rows : ID, Special_note.

Not all products have a special note. When a product doesn't have a special note, there is no row for that product in table 2.

I'm trying to use a select query that will get all the information from table_1 but also grab the special note from table_2 when there's one.

The problem I'm having right now is that if there is no special note, it won't grab the information in table_1 at all.

I understand why it's doing it but I don't know how to fix the query so that it returns all the products whether there is a special note or not.

 SELECT TABLE_1.ID, QUANTITY, PRICE, SPECIAL_NOTE
 FROM TABLE_1, TABLE_2
 WHERE TABLE_1.ID = TABLE_2.ID

I simplified the query a little bit for the purpose of this example.

Thanks for your help!

Answer 1

Use a LEFT OUTER JOIN:

SELECT t1.ID, t1.QUANTITY, t1.PRICE, t2.SPECIAL_NOTE
FROM TABLE_1 t1
LEFT OUTER JOIN TABLE_2 t2 ON t1.ID = t2.ID

Update:

To add a WHERE clause, e.g., where quantity >= 1, do this:

SELECT t1.ID, t1.QUANTITY, t1.PRICE, t2.SPECIAL_NOTE
FROM TABLE_1 t1
LEFT OUTER JOIN TABLE_2 t2 ON t1.ID = t2.ID
WHERE t1.QUANTITY >= 1