2 Replies Latest reply on Jul 10, 2006 3:40 PM by wjs

    More db query help (related products)

    wjs
      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?
        • 1. Re: More db query help (related products)
          Level 7
          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. Re: More db query help (related products)
            wjs Level 1
            Thanks! So stupid, and right in front of my face the whole time.

            ARGH!