nested selects on mysql_4.0

I'm stuck with a mysql 4.0 server--which does not allow
nested selects. I want to attach arbitrary 'attributes'
to a 'widgets' table and then query for all widgets
that have two or more specified attributes.

create table widget
(
widget_id int not null auto_increment primary key,
widget_name varchar(48)
);

create table attribute
(
attribute_id int not null auto_increment primary key,
attribute_name varchar(48)
);

## attach attributes to the widget table
create table attribute_list
(
widget_id int not null references widget(widget_id),
attribute_id int not null references attribute(attribute_id),
);


So, each widget can have multiple attributes attached via
the attribute_list "junction link" table.

I have to use mysql_4.0 which does not allow nested selects.
How can I query for all widgets that have, for instance, both the "red"
and "big"
attribute attached, via the attribute_list table?

There must be very obvious and much simpler solution - but I'm stumped
as to what it is.

Anyway, I think this works: (6 and 9 represent 'big' and 'red' in your
example)

SELECT widget_id,GROUP_CONCAT(attribute_id),COUNT(attribu te_id) AS cnt
FROM widget_attribute WHERE attribute_id IN (6,
9) GROUP BY widget_id HAVING cnt > 1;

Sandy.Pittendrigh@gmail.com wrote:
> How can I query for all widgets that have, for instance, both the "red"
> and "big" attribute attached, via the attribute_list table?

Here's another solution besides the one posted by strawberry:

SELECT w.*
FROM widget AS w
JOIN attribute_list AS l ON w.widget_id = l.widget_id
JOIN attribute AS red ON l.attribute_id = red.attribute_id
JOIN attribute AS big ON l.attribute_id = big.attribute_id

Regards,
Bill K.

Actually, I think my solution might be flawed. What happens when two or
more attributes are assigned but only one of them matches the criteria?

A useful tip: When both Bill and I post on the same topic, ignore mine
and go with Bill's. Well, it works for me :-)

Bill Karwin wrote:
> SELECT w.*
> FROM widget AS w
> JOIN attribute_list AS l ON w.widget_id = l.widget_id
> JOIN attribute AS red ON l.attribute_id = red.attribute_id
> JOIN attribute AS big ON l.attribute_id = big.attribute_id

I forgot to add a clause to ensure that the red and bug correlation
names are the right attributes:

WHERE red.attribute_name = 'red' AND big.attribute_name = 'big'

Regards,
Bill K.

strawberry wrote:
> Actually, I think my solution might be flawed. What happens when two or
> more attributes are assigned but only one of them matches the criteria?
>
> A useful tip: When both Bill and I post on the same topic, ignore mine
> and go with Bill's. Well, it works for me :-)

Wow, thanks! But I made a mistake in mine anyway! :-)

Anyway, your solution looked fine to me, if one can assume a primary key
defined over (widget_id, attribute_id) in the intersection table.

Regards,
Bill K.

Thanks guys. I'm going to have to try this and think about
it in another night or two. I have to leave town
for a few days tomarrow moroning (with custom spelling
particularly appropriate for monday mornings).

But this is helpful. These cascading aliases remind me
a little of Duff's device, which I still haven't got my head
completely around, now some ten years later. If I try it
and it works, I don't necessarily have to know why.

I think you would need a self join on attrib_list. So you would have
to do this :

select a.widget_id
from attrib_list as a
inner join attrib_list as b on a.widget_id=b.widget_id
inner join attrib as c on a.attrib_id=c.attrib_id
inner join attrib as d on a.attrib_id=d.attrib_id
where c.attrib_name='red'
and d.attrib_name='big';

create table widget
(
widget_id int not null primary key auto_increment,
name varchar(48) not null
);

create table attribute
(
attribute_id int not null primary key auto_increment,
attribute varchar(60) not null
);

create table attribute_list
(
widget_id int not null references widget(id),
attribute_id int not null references attribute(attribute_id)
);

insert into widget (widget_id,name) values(null,'bigred');
insert into widget (widget_id,name) values(null,'smallgreen');
insert into attribute (attribute_id,attribute) values(null,'red');
insert into attribute (attribute_id,attribute) values(null,'big');
insert into attribute (attribute_id,attribute) values(null,'green');
insert into attribute (attribute_id,attribute) values(null,'small');
insert into attribute_list (widget_id, attribute_id) values(1,1);
insert into attribute_list (widget_id, attribute_id) values(1,2);
insert into attribute_list (widget_id, attribute_id) values(2,3);
insert into attribute_list (widget_id, attribute_id) values(3,4);

mysql> select * from widget;
+-----------+------------+
| widget_id | name |
+-----------+------------+
| 1 | bigred |
| 2 | smallgreen |
+-----------+------------+

mysql> select * from attribute;
+--------------+-----------+
| attribute_id | attribute |
+--------------+-----------+
| 1 | red |
| 2 | big |
| 3 | green |
| 4 | small |
+--------------+-----------+

mysql> select * from attribute_list;
+-----------+--------------+
| widget_id | attribute_id |
+-----------+--------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+-----------+--------------+

TEST1
mysql> SELECT w.*
-> FROM widget AS w
-> JOIN attribute_list AS l ON w.widget_id = l.widget_id
-> JOIN attribute AS red ON l.attribute_id = red.attribute_id
-> JOIN attribute AS big ON l.attribute_id = big.attribute_id;
+-----------+------------+
| widget_id | name |
+-----------+------------+
| 1 | bigred |
| 1 | bigred |
| 2 | smallgreen |
+-----------+------------+

TEST2
mysql> SELECT w.*
-> FROM widget AS w
-> JOIN attribute_list AS l ON w.widget_id = l.widget_id
-> JOIN attribute AS red ON l.attribute_id = red.attribute_id
-> JOIN attribute AS big ON l.attribute_id = big.attribute_id
-> where red.attribute = 'red'
-> and big.attribute = 'big';
Empty set (0.00 sec)

TEST3
mysql> SELECT w.*
-> FROM widget AS w
-> JOIN attribute_list AS list ON w.widget_id = list.widget_id
-> JOIN attribute AS red ON list.attribute_id = red.attribute_id
-> JOIN attribute AS big ON list.attribute_id = big.attribute_id
-> where red.attribute = 'red'
-> and big.attribute = 'big';
Empty set (0.00 sec)

OK: here's what seems to work

SELECT w.*
FROM widget AS w
JOIN attribute_list AS alist1 ON w.widget_id = alist1.widget_id
JOIN attribute_list AS alist2 ON w.widget_id = alist2.widget_id
JOIN attribute_list AS alist3 ON w.widget_id = alist3.widget_id

JOIN attribute AS red ON alist1.attribute_id = red.attribute_id
JOIN attribute AS big ON alist2.attribute_id = big.attribute_id
JOIN attribute AS magenta ON alist3.attribute_id = magenta.attribute_id

where red.attribute = 'red' and big.attribute = 'big' and
magenta.attribute='magenta'