First guess, without setting up a testing environment, is
that your ON
clause is off. Isn't it comparing product_id to product_id. I
thing
you want to link prp.product_related_id to p.product_id do
you not?
> SELECT prp.product_related_id
> FROM product_relatedProduct prp
> INNER JOIN product p ON prp.product_id = p.product_id
> WHERE prp.product_id = #url.ProdID#
> AND p.display = 1
> AND p.price > 0
wjs wrote:
> Hope you guys can help again.
>
> I have a table (product_relatedproduct) that associates
product ID numbers
> with each other to create a "related products" section
of a website I'm
> developing. The db table scheme is very simple:
>
>
> product_related_product
> -----------------------------------
> product_id
> product_related_id
>
>
> So, for example:
>
> 1110 1111
> 1110 1138
> 1110 1140
> etc...
>
> You get the idea. What I'm trying to do is write a query
that does the
> following:
>
> 1. Find all of the product_related_id numbers associated
with whatever product
> page you are on. So, if I'm on product # 1110's page,
the query will return
> 1111, 1138, 1140. That's easy.
>
> 2. I need to also have the query reference the product
table against these
> three product ID's to check that they (a) are set to
display, and (b) have a
> price of greater than zero dollars (display and price
being the names of those
> fields in the product table).
>
> My query so far is:
>
> SELECT prp.product_related_id
> FROM product_relatedProduct prp
> INNER JOIN product p ON prp.product_id = p.product_id
> WHERE prp.product_id = #url.ProdID#
> AND p.display = 1
> AND p.price > 0
>
> I'm having trouble with the second part. My results seem
to ignore the checks
> for display and price, and I'm not sure why.
>
> Any takers?
>
2
Replies
AdChoices