EasyCFM.COM - The ColdFusion Community! About Us!

Senior Member
Posts: 373
Joined: Jan 2006

Hello!

I am working with 2 tables:

Table 1 - Products Table containing 8 fields including a PK (PID).
Table 2 - Recommended Products Table containing 2 fields, PID_A and PID_B

I tried joining both tables so that I can reference the URL.PID of the selected product to show the recommended products linked to that URL.PID which is PID_B.

Here is the query:

     SELECT Products.Brand,
                     Products.Standard,
                     Products.Description,
                     Products.Price,
                     Recommended.PID_A,
                     Recommended.PID_B,
                     Products.PID
     FROM Products
     INNER JOIN Recommended
     ON Products.PID = Recommended.PID_A
     WHERE PID_A = #PID# AND PID_B <> #PID#

I have tried several combinations of WHERE statements, but I have not written the right one. Any help will be appreciated!

Thanks

daddyFL

03-21-2006 @ 9:07 AM

Senior Member
Posts: 373
Joined: Jan 2006

I think I may have solved the issue, I will post back with the code if it's solved.

Thanks

daddyFL

03-21-2006 @ 9:10 AM

Senior Member
Posts: 373
Joined: Jan 2006

Slight change in the query:

     SELECT Products.Brand,
                     Products.Standard,
                     Products.Description,
                     Products.Price,
                     Recommended.PID_A,
                     Recommended.PID_B,
                     Products.PID
     FROM Products
     INNER JOIN Recommended
     ON Products.PID = Recommended.PID_B
     WHERE PID_A = #PID# AND PID_B <> #PID#

I changed:
ON Products.PID = Recommend.PID_A

to the following:
ON Products.PID = Recommended.PID_B

Thanks

spingiggy

03-21-2006 @ 9:51 AM

Junior Member
Posts: 70
Joined: Feb 2006

Hello,
I could be missing something but in regards to your recommended table, do you have a Products.PID field in the Recommended table?

Can't you just query the products table to get the information from the recommended table.

--Simple Version --
SELECT Products.PID
FROM Recommended
WHERE Products.PID = #URL.PID#

--Better Version--
SELECT Products.PID
FROM Recommended
WHERE Products.PID = <cfqueryparam value="#URL.PID#" cfsqltype="cf_sql_char">

I also have a Category ID field in the recommended table so that I can have recommened products per category as well. And then the same for featured products, new arrivals and so on.

Chris

This message was edited by spingiggy on 3-21-06 @ 9:54 AM

kevsarg18

03-21-2006 @ 12:33 PM

Senior Member
Posts: 428
Joined: Jun 2004

that will only give you the ids of the recommended products per givin product ID. You still need to join the product table to ge tthe product information.

My CFML Scripts.

daddyFL

03-21-2006 @ 12:58 PM

Senior Member
Posts: 373
Joined: Jan 2006

can you look at my code from the earlier post and tell me if that join is correct?

Thanks

spingiggy

03-21-2006 @ 1:08 PM