Join 3 Tables

[The Little Guy]

OK.. Im trying to join 3 tables PRODUCTS, IMAGES, and VIEWED_PRODUCTS.

 

Table: VIEWED_PRODUCTS

IP  	                       SESSION  	      DATE  	           PRODUCT  	       ID
68.117.46.68 	8fj2fd8c9sffc5k994iutcl9r4 	2007-02-24 22:41:12 	     3 	               1

 

That is one row in the table, and I need everything to be based off of PRODUCT and ID from the other 2 tables.

So... How do I get my code to work?

 

According to my above table:

- VIEWED_PRODUCTS.ID = 1

- PRODUCTS.PRODUCT_NUM = 3

- IMAGES.PRODUCT_NUM = 3

 

When I run the SQL, I get no output, not even an error.

 

SELECT PRODUCTS . * , IMAGES . * , VIEWED_PRODUCTS . *
FROM PRODUCTS, IMAGES, VIEWED_PRODUCTS
WHERE VIEWED_PRODUCTS.ID = '1'
AND PRODUCTS.PRODUCT_NUM = VIEWED_PRODUCTS.PRODUCT
AND IMAGES.PRODUCT_NUM = VIEWED_PRODUCTS.PRODUCT
ORDER BY VIEWED_PRODUCTS.DATE
LIMIT 3

[artacus]

Didn't I just yell at you a little while ago for joining tables w/o using JOIN?

Write your query the correct way and we'll go from there.

[The Little Guy]

No

[The Little Guy]

How do you do that? I have never done that before, because nowhere does it say you have to, and I can not find an example of how it is done.

[fenway]

And you really shouldn't be using all uppercase for queries... artacus means something like:

 

SELECT p.* , i.* , vp.*
FROM viewed_products AS vp
LEFT JOIN products AS p ON ( p.product_num = vp.product  ) 
LEFT JOIN images as i ON ( i.product_num = p.product_num )
WHERE vp.id = '1'
ORDER BY vp.date 
LIMIT 3

 

I'm guessing you might not want left joins everywhere, I don't know what you're trying to do.

[The Little Guy]

Well... I think I got it to work, atleast so far it works.

 

Here is what I did anyways:

SELECT *
FROM PRODUCTS
INNER JOIN IMAGES
INNER JOIN VIEWED_PRODUCTS
ON 
PRODUCTS.PRODUCT_NUM = VIEWED_PRODUCTS.PRODUCT
WHERE
VIEWED_PRODUCTS.ID = '{$_SESSION['id']}'
AND 
IMAGES.PRODUCT_NUM = VIEWED_PRODUCTS.PRODUCT
AND
IMAGES.MAIN = '1'
ORDER BY VIEWED_PRODUCTS.DATE DESC LIMIT 3

 

Thanks for the help

[fenway]

I can't believe that you can join without an on clause... and if you change left join to inner join in the query I provided, that you give the same results.

[The Little Guy]

I do have an ON clause in there, its after the second INNER JOIN VIEWED_PRODUCTS

[fenway]

And what about after the first inner join?