Join 3 tables with one query?

[coogie]

Hello there.

Currently I have 3 tables and i'd like to make it one query for the ProductID. Here are my tables:

 

Saved_product:

ProductID, email, colour, initials, shape, profile_name

 

profiles:

profile_name, email, opt1, opt2, opt3, opt4

(there is no primary key, but the profile_name is unique for that email/user. I realize this is a bad design, but i used a chinese web design company)

 

ordered_products:

OrderID, ProductID, quantity, total

 

 

What i would like to do (in english) is this:

 

Selected all from saved_product, ordered_products and profiles WHERE productID = 1111002

The problem i have is the 'profiles' table does not had productID inside of it. It only has the email. So i need to look at the 'ordered_products' to find what profile name and email address to look for.

 

At the moment i do it with these two queries. But i need it to be one query depending on the selected productID

"SELECT * from profiles WHERE email = '$email' AND profile_name = '$profile''";

"SELECT * FROM Saved_product INNER JOIN ordered_products ON Saved_product. ProductID = ordered_products. ProductID WHERE Saved_product. ProductID = '$ID'";

 

 

please help!

 

 

 

[fnairb]

Give this a go

 

SELECT *
FROM Saved_product, profiles, ordered_products
WHERE Saved_product.ProductID = ordered_products.ProductID
  AND Saved_product.email = profiles.email
  AND Saved_product.ProductID = 1111002

 

You may need to use:

SELECT Saved_product.*, profiles.*, ordered_products. *

[fenway]

You should really re-write that as a proper join.

[coogie]

in the future i will re-write it so that the profile table has a unique ID.

 

But is it possible to make a proper join without doing that? I'm not entirely sure what a proper join is, my sql knowledge is low!

 

Thanks a lot for the code fnairb, i will try it now!

[fnairb]

I'm not sure what a "proper" join is either.  The query I provided joins the tables implicitly.  True, it does not explicitly use a JOIN keyword.  maybe that is what fenway is referring too.  I would love to see how that query would be written so I could use it in the future.

[fenway]

The query I provided joins the tables implicitly.  True, it does not explicitly use a JOIN keyword.

"Implicitly" is precisely the problem:

 

SELECT *
FROM Saved_product AS sp 
INNER JOIN profiles AS p ON ( p.email = sp.email ) 
INNER JOIN ordered_products AS op ON ( sp.ProductID = op.ProductID ) 
WHERE sp.ProductID = 1111002

This will keep you out of trouble.

[coogie]

Thanks for the replies

 

The code from fnairb does work

 

what is the difference/advantages between the two codes?

 

The problem i have is the profile need to be looked up by the email AND the profile name..

[fnairb]

If I get your question all you need to do is replace the

ProductID = 1111002

 

with

email = 'whatever' and profile_name = 'whatever'

 

Using the join that would look like:

SELECT *
FROM Saved_product AS sp 
INNER JOIN profiles AS p ON ( p.email = sp.email ) 
INNER JOIN ordered_products AS op ON ( sp.ProductID = op.ProductID ) 
WHERE sp.email = 'whatever'
  AND sp.profile_name = 'whatever'

 

The other would look like:

SELECT *
FROM Saved_product, profiles, ordered_products
WHERE Saved_product.ProductID = ordered_products.ProductID
  AND Saved_product.email = profiles.email
  AND Saved_product.email = 'whatever'
  AND Saved_product.profile_name = 'whatever'

[fenway]

what is the difference/advantages between the two codes?

It's much better to use JOIN -- no issues with comma precedence changes, no missing join conditions, clear where clauses for filtering only, etc.  Also, makes it trivial to switch join type (inner vs left) in a second.