Professional Web Applications Themes

nested selects on mysql_4.0 - MySQL

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" ...

  1. #1

    Default 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?

    Sandy.Pittendrigh@gmail.com Guest

  2. #2

    Default Re: nested selects on mysql_4.0

    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;

    strawberry Guest

  3. #3

    Default Re: nested selects on mysql_4.0

    [email]Sandy.Pittendrigh[/email] 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.
    Bill Karwin Guest

  4. #4

    Default Re: nested selects on mysql_4.0

    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 :-)

    strawberry Guest

  5. #5

    Default Re: nested selects on mysql_4.0

    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.
    Bill Karwin Guest

  6. #6

    Default Re: nested selects on mysql_4.0

    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.
    Bill Karwin Guest

  7. #7

    Default Re: nested selects on mysql_4.0

    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.

    Sandy.Pittendrigh@gmail.com Guest

  8. #8

    Default Re: nested selects on mysql_4.0

    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';

    roch77@gmail.com Guest

  9. #9

    Default Re: nested selects on mysql_4.0

    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)

    Sandy.Pittendrigh@gmail.com Guest

  10. #10

    Default Re: nested selects on mysql_4.0

    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'

    Sandy.Pittendrigh@gmail.com Guest

Similar Threads

  1. Update multiple selects
    By cfquest in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: July 15th, 05:54 PM
  2. 2 selects related
    By Swd1974 in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 16th, 06:01 PM
  3. SQL: nested select vs. two selects
    By darrel in forum Adobe Dreamweaver & Contribute
    Replies: 2
    Last Post: April 28th, 03:42 PM
  4. cursor or 2 selects
    By Dean Savovic in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 07:29 AM
  5. How to count record selects?
    By TimC in forum Oracle Server
    Replies: 3
    Last Post: December 19th, 09:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139