SQL query not working... WHY?

[lazzerous]

I've been playing with this query for 3 hours to no avail - does anyone see an error in it that I don't?

SELECT DISTINCT
organization.id
FROM
organization,
unit,
unit_contains_service,
unit_speaks_language
WHERE
(
organization.name LIKE '%exchange%'
OR organization.address_one LIKE '%exchange%'
OR organization.zip LIKE '%exchange%'
)
OR
(
unit_contains_service.fk_service_id='exchange'
AND unit_contains_service.fk_unit_id=unit.id
)
OR
(
unit_speaks_language.fk_language_id='exchange'
AND unit_speaks_language.fk_unit_id=unit.id
)
AND
unit.fk_organization_id=organization.id;

table descriptions for reference

[FONT=Courier New]mysql> desc organization;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(4) | | PRI | NULL | auto_increment |
| name | varchar(50) | YES | MUL | NULL | |
| address_one | varchar(150) | YES | | NULL | |
| address_two | varchar(50) | YES | | NULL | |
| fk_city_id | int(4) | YES | | NULL | |
| fk_county_id | int(3) | YES | | NULL | |
| zip | int(5) | YES | | NULL | |
| phone | int(10) | YES | | NULL | |
| phone2 | int(10) | YES | | NULL | |
| tollFreePhone | int(10) | YES | | NULL | |
| tollFreePhone2 | int(10) | YES | | NULL | |
| fax | int(10) | YES | | NULL | |
| email | varchar(75) | YES | | NULL | |
| description | longtext | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

mysql> desc unit;
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(4) | | PRI | NULL | auto_increment |
| fk_organization_id | int(4) | YES | | NULL | |
| name | varchar(75) | YES | | NULL | |
| address_one | varchar(150) | YES | | NULL | |
| address_two | varchar(50) | YES | | NULL | |
| fk_city_id | int(4) | YES | | NULL | |
| fk_county_id | int(3) | YES | | NULL | |
| zip | int(5) | YES | | NULL | |
| phone | int(10) | YES | | NULL | |
| phone2 | int(10) | YES | | NULL | |
| tollFreePhone | int(10) | YES | | NULL | |
| tollFreePhone2 | int(10) | YES | | NULL | |
| fax | int(10) | YES | | NULL | |
| email | varchar(75) | YES | | NULL | |
| description | longtext | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

mysql> desc unit_contains_service;
+---------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------+------+-----+---------+----------------+
| id | int(5) | | PRI | NULL | auto_increment |
| fk_unit_id | int(4) | YES | | NULL | |
| fk_service_id | int(4) | YES | | NULL | |
+---------------+--------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc unit_speaks_language;
+----------------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------+------+-----+---------+----------------+
| id | int(4) | | PRI | NULL | auto_increment |
| fk_unit_id | int(4) | YES | | NULL | |
| fk_language_id | int(3) | YES | | NULL | |
+----------------+--------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
[/FONT]

[lazzerous]

update - here is the EXPLAIN on the query.. not sure what all this means or how to speed things up. When I do a 'show processlist' it just says 'copying to tmp table..

[FONT=Fixedsys]
mysql> explain SELECT DISTINCT organization.id FROM organization, unit, service, unit_contains_service, language, unit_speaks_language WHERE (organization.name LIKE '%exchange%' OR organization.address_one LIKE '%exchange%' OR organization.zip LIKE '%exchange%') OR (service.nameOfService='exchange' AND service.id=unit_contains_service.fk_service_id AND unit_contains_service.fk_unit_id=unit.id) OR (language.name='exchange' AND language.id=unit_speaks_language.fk_language_id AND unit_speaks_language.fk_unit_id=unit.id) AND unit.fk_organization_id=organization.id ORDER BY organization.name;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 12
Current database: hivilwebsite

+-----------------------+-------+-----------------------+----------------+---------+------+------+-----------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------------------+-------+-----------------------+----------------+---------+------+------+-----------------------------------+
| service | ALL | id,nameOfService | NULL | NULL | NULL | 42 | Using temporary; Using filesort |
| language | ALL | name,id | NULL | NULL | NULL | 9 | |
| unit | ALL | id,fk_organization_id | NULL | NULL | NULL | 666 | |
| organization | ALL | id | NULL | NULL | NULL | 664 | where used |
| unit_speaks_language | index | fk_language_id | fk_language_id | 10 | NULL | 666 | where used; Using index; Distinct |
| unit_contains_service | index | fk_service_id | fk_service_id | 10 | NULL | 1327 | where used; Using index; Distinct |
+-----------------------+-------+-----------------------+----------------+---------+------+------+-----------------------------------+
6 rows in set (0.00 sec)
[/FONT]

[Roger_Ramjet]

You need to learn about and use JOINS properly. At present you are letting things default to 'cartesian' joins by using the commas in your table list and not suppliying any join conditions. Cartesian means that every row in each table is joined to every row in all the other tables. Even if your query worked, it would take a long time because of that.

I'm not going to write any code for you on this because it is more important that you spend 3 hours reading up on joins and then using them, instead of wasting 3 hours messing about in the dark cos you don't really understand sql or relation databases.

PS there are no such thing as good shortcuts in any programming - untill you know how to do it the long way first, And using default join syntax is one of the worst short-cuts there is.

[Sxooter]

You also need another level of parens around your OR statement if you're gonna do your join in your where clause. So, either:

select .... join on (a.id=b.aid) where (clause) OR (clause) OR (clause)

OR:

select .... where ((clause) OR (clause) OR (clause)) AND (a.id=b.aid)

Otherwise, the ORs will always win, and you'll get a partial or full cartesian product