Another MySQL Join Question

[jeff5311]

Sorry for all the questions guys! I'm a SQL newb...

I have 2 tables I'm trying to join:

traits
trait_num trait_name trait_value
1 color blue
2 color red
3 color green
4 size small
5 size medium
6 size large
7 buttons round
8 buttons square
9 buttons oval

products_to_traits
product_num trait_num
1 1
1 4
1 7
2 1
2 6
2 8
3 3
3 5
3 7


for example, with an simple join on trait_num, product #1 would return the following product characteristics for me:

traits_name traits_value
color blue
size small
buttons round


here's my problem...

how do i create a SQL statement that will show me the available trait_name's and trait_value's if i'm given a set of trait_num's, yet exclude the trait_name's that have already been selected?

For example, given trait_num 7 (trait_name = "buttons" and trait_value = "round"), there are two products that have this characteristic (product_num 1, and product_num 3). I want a list all of these product's trait_num's excluding and trait_nums that have the trait_name = "buttons. So, I want as a result:

trait_num
1
4
3
5

Does this make sense?

Thanks in advance for all your help!!!
Jeff

 

[TonyGroves]

How about:
[tt]
SELECT p2.trait_num
FROM
products_to_traits p1
JOIN products_to_traits p2
ON p1.trait_num=7 AND p1.product_num=p2.product_num
JOIN traits t ON p2.trait_num=t.trait_num
WHERE
t.trait_name
NOT IN
(
SELECT DISTINCT t.trait_name
FROM
products_to_traits p
JOIN traits t
ON t.trait_num=p.trait_num AND p.trait_num=7
)
[/tt]

 

[jeff5311]

Tony,

Thanks for your help!

That looks like it might work, but I can can't get MySQL to evaluate the sub-select. It gives me a syntax error #1064 (I'm using phpMyAdmin to run the query) as follows:


MySQL said:


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT t.trait_name
FROM products_to_traits p
JOIN tr


Does MySQL not like sub-selects?

Also, how would I write it for multiple traits (given traits 1 and 7).

Thanks so much!!!
Jeff

 

[r937]

Does MySQL not like sub-selects?

not before version 4.1, no

r937.com | rudy.ca

 

[jeff5311]

Oh no! I have version 4.0.15 of MySQL.

Is there another way to write the query without a sub-select?

Thanks!!!
Jeff