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!

D'Arcy Rittich · Accepted Answer · 2010-03-24 15:55:49Z

2

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

edited Mar 24, 2010 at 15:55

answered Mar 24, 2010 at 15:24

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Enkay Over a year ago

Thank you very much. Trying to grasp this new concept. Let's say I wanted to only grab the rows where quantity is at least 1. Where would I add the where query? After the LEFT OUTER JOIN line?

Enkay Over a year ago

I have figured it out in a simple example! Trying to make it work with my actual very long and complicated query. Thanks a lot!

Collectives™ on Stack Overflow

Query in multiple tables with MYSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related