multiple joins in a cfquery

[PushCode]

Can someone steer me in the right direction with this query? I'm trying to select any product from the product table who's product_id matches any product_id's from the other three tables.

What i have doen't work. Any ideas?

<cfquery name="get_prod_by_catmatocc" datasource="#ds#">
SELECT a.product_id, a.title, a.price, a.img_thumb
FROM products a, product_materials_matrix b, product_sub_categories c,
product_category_matrix d
WHERE a.product_id = b.product_id
AND a.product_id = c.product_id
AND a.product_id = d.product_id
AND b.material_id = #url.matid#
AND c.sub_category_id = #url.occid#
AND d.category_id = #url.catid#
</cfquery>

 

[r937]

select a.product_id
     , a.title
     , a.price
     , a.img_thumb
  from products a
 where exists
     ( select * 
         from product_materials_matrix 
        where product_id = a.product_id 
          and material_id = #url.matid# )
    or exists
     ( select *
         from product_sub_categories 
        where product_id = a.product_id 
          and sub_category_id = #url.occid# )
    or exists
     ( select *
         from product_category_matrix 
        where product_id = a.product_id      
          and category_id = #url.catid# )

be careful with sql injection from your url strings

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[PushCode]

Thanks!

 

[PushCode]

r937,

Your query actually seem to return every item in the products table, as opposed to returning only select items. Any idea why this may be?

 

[r937]

probably because all the products have at least one related row in one of the three tables

you said "matches any product_id's from the other three tables"

as you can see, i interpreted this as a combination of OR conditions

perhaps you meant AND?


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)