MySQL JOIN Question 1

[jeff5311]

This query I'm trying to construct seems simple, but I can't get it to work. Can someone please help?!?!?!

I have two tables...

My "products_to_traits" table has the following data:

product_num trait_num
1 1
1 12
1 23
1 30
2 1
2 30
2 43


My "products" table has the following data:

product_num product_name
1 Widget
2 Thingamabob


How would I create a query that would show only the products that have the trait_num's 30 & 12 ("widget")?

Thanks in advance!!!
Jeff

 

[mbrooks]

? is the trait number.

SELECT * FROM products AS p LEFT JOIN traits AS t ON (p.product_num = t.product_num) WHERE t.trait_num = ?

M. Brooks

http://mbrooks.info

 

[r937]

another fine example of the "red/blue car problem"

select product_name
  from products
inner
  join products_to_traits
    on products_to_traits.product_num
     = products.product_num
 where trait_num in ( 30, 12 )
group
    by product_name
having count(distinct trait_num) = 2

if the combination of product_num and trait_num in the products_to_traits table is unique (as it would be if declared as the PK for that table), then you don't need DISTINCT in the COUNT




r937.com | rudy.ca

 

[jeff5311]

Thanks for your help guys!

r397 - that's exactly what I was looking for... it works perfectly.

Thanks Again!
Jeff