Computer Hope

Software => Computer programming => Topic started by: ultimatum on April 19, 2011, 06:02:06 PM

Title: MySQL help needed: LEFT JOIN and INNER JOIN
Post by: ultimatum on April 19, 2011, 06:02:06 PM

Hey guys,

I need help with mysql statement. I have 3 tables: new_orders, client_info, admin_notes.

I'm trying to join all three tables together but having admin_notes as an optional result - meaning it may or may not be there.

To join first two tables I used INNER JOIN, like this:

Quote

SELECT * FROM new_orders INNER JOIN client_info ON new_orders.client_id = client_info.client_id

Now, I need to attached admin_notes to this result set but only some orders have notes and other's don't. I noticed you can't combine INNER JOIN with LEFT JOIN in one statement  >:(

I tried doing:

Quote

SELECT * FROM new_orders INNER JOIN client_info ON client_info.client_id = new_orders.client_id, new_orders LEFT JOIN admin_notes ON new_orders.order_id = admin_notes.order_id

But this return an error saying new_orders is not unique table/alias.

Any ideas how to accomplish this??

Thanks in advance.

Title: Re: MySQL help needed: LEFT JOIN and INNER JOIN
Post by: ultimatum on April 19, 2011, 08:41:39 PM

I made an adjustment to make it work but I'm still not sure if that's the "correct" way of doing it.

Basically, now I take new_orders LEFT JOIN client_info then LEFT JOIN admin_notes. It returns correct results for now. There isn't much data in the database so I'll have to wait until someone suggests something better or more data is gathered :/

Title: Re: MySQL help needed: LEFT JOIN and INNER JOIN
Post by: DaveLembke on April 23, 2011, 02:42:04 AM

Quote

meaning it may or may not be there

- SQLyog is a great tool to view databases and see what is there and make changes with with a GUI. The FREE community edition is limited, but still has many useful features. If you ever lose track of a table etc you can use it to see where it went etc.

Title: Re: MySQL help needed: LEFT JOIN and INNER JOIN
Post by: ultimatum on April 23, 2011, 09:57:51 AM

Looks like a great tool DaveLembke, something I may require down the road but how does that help my problem in joining three tables? Thanks.