I have 4 tables, each defined as:
CREATE TABLE service_data_text (
data text NOT NULL,
service_field int UNSIGNED NOT NULL,
id int UNSIGNED NOT NULL,
rid bigint UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (rid)
);
The only difference between the tables is the datatype of the 'data' field (it varies from int, float, text, and string). I am trying to query the 'data' field for a certain value in each of the four tables and return an ID number from them. I also have a table called 'service' which is defined as such:
CREATE TABLE service (
insert_date int NOT NULL,
status enum('active','cancelled') NOT NULL default 'active',
service_profile int NOT NULL,
id int UNSIGNED NOT NULL,
rid int UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (rid)
);
I can enter this query and get this result, as you can see, the service_data_integer.id is equal to the service.rid.
mysql> SELECT service_data_integer.id as THIS, service.rid as IS_EQUAL_TO,service_data_integer.rid FROM service_data_integer,service WHERE service.rid=service_data_integer.id AND service_data_integer.data=33;
+------+-------------+-----+
| THIS | IS_EQUAL_TO | rid |
+------+-------------+-----+
| 1 | 1 | 1 |
+------+-------------+-----+
1 row in set (0.00 sec)
This query attempts to LEFT JOIN the 4 tables, while querying each 'data' field and seeing if it is in there. What I want, is the service.rid.
mysql> SELECT service.rid FROM service,service_data_string LEFT JOIN service_data_integer USING(rid) LEFT JOIN service_data_text USING(rid) LEFT JOIN service_data_float USING(rid) WHERE (service.rid=service_data_integer.id AND service_data_integer.data='33') OR (service.rid=service_data_text.id AND service_data_text.data='33') OR (service.rid=service_data_float.id AND service_data_float.data='33') OR (service.rid=service_data_string.id AND service_data_string.data='33');
Empty set (0.00 sec)
Can someone tell me why this isn't working? It should return the one result like it does in the first query...any ideas?
Any help is appreciated...thanks.
