On 5 Mar 2005 01:44:11 -0800, "frizzle" <phpfrizzle@hotmail.com> wrote:
[color=blue]
>But it was too good to be true... I cannot get it to work, and
>i don't know where to look.. I tried a few things which could possibly
>somehow make a little bit sense, but they didn't :(
>
>I get the following error notice:
>You have an error in your SQL syntax near 'FROM models m JOIN series s
>ON (m.series_id = s.series_id AND s.'
>
>Please help me, i really don't know where to fix this.
>
>(btw, I found it hard to find some good join tuts also)[/color]
Well, I put together some tables based on what you said and ran it, and with a
couple of tweaks it at least runs. I'm still not 100% clear on your data as you
haven't posted any actual examples so it might not do the right thing.
mysql> desc series;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | PRI | 0 | |
| active | int(10) unsigned | | | 0 | |
| name | varchar(24) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc models;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | PRI | 0 | |
| active | int(10) unsigned | | | 0 | |
| series_id | int(10) unsigned | | | 0 | |
| cat_id | int(10) unsigned | | | 0 | |
| size | varchar(24) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into series values (1, 1, 'series 1');
Query OK, 1 row affected (0.03 sec)
mysql> insert into models values (1, 1, 1, 1, 'big');
Query OK, 1 row affected (0.02 sec)
mysql> insert into models values (2, 1, 1, 2, 'small');
Query OK, 1 row affected (0.01 sec)
mysql> insert into models values (3, 1, 1, 3, 'medium');
Query OK, 1 row affected (0.00 sec)
mysql> select * from series;
+----+--------+----------+
| id | active | name |
+----+--------+----------+
| 1 | 1 | series 1 |
+----+--------+----------+
1 row in set (0.00 sec)
mysql> select * from models;
+----+--------+-----------+--------+--------+
| id | active | series_id | cat_id | size |
+----+--------+-----------+--------+--------+
| 1 | 1 | 1 | 1 | big |
| 2 | 1 | 1 | 2 | small |
| 3 | 1 | 1 | 3 | medium |
+----+--------+-----------+--------+--------+
3 rows in set (0.00 sec)
mysql> SELECT s.name,
-> m2.id,
-> m2.size
-> FROM models m -- one row for the first product (see WHERE clause at
end)
-> JOIN series s -- find the associated series
-> ON (m.series_id = s.id AND s.active = 1)
-> JOIN models m2 -- multiple rows for the other products in the series
-> ON (m2.series_id = s.id
-> AND m2.id != m.id
-> AND m2.active = 1)
-> WHERE m.cat_id = 1
-> AND m.id = 1
-> AND m.active = 1;
+----------+----+--------+
| name | id | size |
+----------+----+--------+
| series 1 | 2 | small |
| series 1 | 3 | medium |
+----------+----+--------+
2 rows in set (0.00 sec)
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
