Anybody have any ideas about why I can't do this INNER JOIN given my database design as outlined below:
mysql> EXPLAIN SELECT profession.id FROM profession INNER JOIN user ON profession.id = user.profession_id;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT user.id FROM user INNER JOIN prospects ON user.id = prospects.user_id";
"> "
-> /c
-> /c;
ERROR 1064: You have an error in your SQL syntax near '";
"
/c
/c' at line 1
mysql> EXPLAIN SELECT user.id FROM user INNER JOIN prospects ON user.id = prospects.user_id;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
1 row in set (0.01 sec)
mysql> describe user;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| visitor_lastname | varchar(15) | YES | | NULL | |
| visitor_firstname | varchar(15) | YES | | NULL | |
| id | int(11) | | PRI | 0 | |
| profession_id | int(11) | YES | | NULL | |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe profession;
+------------------------------+-----------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+-----------------------------------------------------------------------------------+------+-----+---------+-------+
| visitor_profession_specified | varchar(40) | YES | | NULL | |
| id | int(11) | | PRI | 0 | |
| user_id | int(11) | YES | | NULL | |
| profession | enum('showroom','architect','designer','electrical','sales','purchasing','other') | YES | | NULL | |
+------------------------------+-----------------------------------------------------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe prospects;
+------------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| visitor_country | varchar(25) | YES | | NULL | |
| visitor_zipcode | int(11) | YES | | NULL | |
| visitor_city | varchar(25) | YES | | NULL | |
| visitor_address | varchar(25) | YES | | NULL | |
| visitor_company | varchar(25) | YES | | NULL | |
| visitor_state_province | varchar(25) | YES | | NULL | |
| visitor_telephone | varchar(12) | YES | | NULL | |
| visitor_fax | varchar(12) | YES | | NULL | |
| visitor_email | varchar(50) | YES | | NULL | |
| id | int(11) | | PRI | 0 | |
| user_id | int(11) | YES | | NULL | |
+------------------------+-------------+------+-----+---------+-------+
11 rows in set (0.01 sec)
Any ideas would be appreciated.
Thanks,
Debra
