MySQL Bugs: #15708: Graphical drag "Table tool 'Join'" on Mac OS X seems broken for just 2 tables

Bug #15708	Graphical drag "Table tool 'Join'" on Mac OS X seems broken for just 2 tables
Submitted:	13 Dec 2005 9:06	Modified:	16 Dec 2005 9:15
Reporter:	Grant Wilson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	1.1.17	OS:	MacOS (Mac OS X 10.3.9)
Assigned to:		CPU Architecture:	Any

Description:
Unable to use 'Join' table command (with graphical drag), unless 2 tables already part of query

How to repeat:
Select first table by double clicking: gives SELECT * FROM answers a [Correct]
Select second table and drag onto query entry box. Graphical table operations bar briefly appears. Release drag on "Join Table". Outcome: Query is not updated. [ie no join happens]

Work around:
Select first table as above.
Add second table (drop on "Add Table"): gives SELECT * FROM answers a, questions q [Correct]
Now attempt join with second table again: gives "SELECT * FROM answers a, questions q, questions q1 WHERE q1.qid=q.qid" [Correct]

Now manually edit SQL to join first and second tables, getting rid of duplicate table
ie edit to become "SELECT * FROM answers a, questions q WHERE a.qid=q.qid"

Suggested fix:
Allow second table to be joined to first, without requiring step need above (see workaround)

If indexes exist on fields, QB properly joins the tables, no need for any workarounds. 

Maybe it just needs a proper error message?

Thank you for a problem report. 

Please, send the results of SHOW CREATE TABLE  statements for your tables. Do you have indexes on the fields used to join tables?

Ok, I'm relatively new to SQL and MYSQL, so I'm not really sure what to say, but...

a) I don't think I have indexes (unless they are automatically created).
b) typing "SHOW CREATE TABLE" (with or without a trailing ";") in the Query Browser gives error 106 'Syntax Error'

Here are my table definitions (entered using 'mysql' text based cli):

 #
 # Table structure for table `questions`
 #
 CREATE TABLE `questions` (
  `qid` tinyint(3) unsigned NOT NULL auto_increment,
  `qtitle` varchar(255) NOT NULL default '',
  `qdate` date NOT NULL default '0000-00-00',
  PRIMARY KEY (`qid`)
 );

 #
 # Table structure for table `answers`
 #
 CREATE TABLE `answers` (
  `aid` tinyint(3) unsigned NOT NULL auto_increment,
  `qid` tinyint(4) NOT NULL default '0',
  `atitle` varchar(255) NOT NULL default '',
  `acount` int(11) NOT NULL default '0',
  PRIMARY KEY (`aid`)
 );

Not sure what SQL to type to create indexes if this needs to be done manually!

You have to enter:

SHOW CREATE TABLE `answers`; 

to get the rtesults like these:

CREATE TABLE `answers` (
  `aid` tinyint(3) unsigned NOT NULL auto_increment,
  `qid` tinyint(4) NOT NULL default '0',
  `atitle` varchar(255) NOT NULL default '',
  `acount` int(11) NOT NULL default '0',
  PRIMARY KEY (`aid`)
 );

According to your information, you have neither indexes no foreign key declared on `qid` column... That is why QB is not able to determine the right way to join the tables, I believe.

So, please, execute the following statement in QB (see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html for details):

ALTER TABLE `answers` ADD FOREIGN KEY `qid` (`qid`) REFERENCES REFERENCES `questions` (`qid`);

and try once more. Please, inform about the results.

Ok, I tried the statement as specified, viz 
>ALTER TABLE `answers` ADD FOREIGN KEY `qid` (`qid`) REFERENCES >REFERENCES`questions` (`qid`);

I got a syntax error (presumably due to the double 'REFERENCES'), so modified the SQL to take care of that and tried again. This time I get an error code '1' (and no message).

Attempting a graphical table join does not work.

Having a brief read of the alter table entry, I notice something about foreign keys and innodb.
I don't know if that is important, but using Mysql Administrator the table type is "MyISAM".

Sorry for double REFERENCE - my fault...

Please, read the QB Help (Chapter 5. Using The MySQL Query Browser, 
5.2. Building Queries):

"Tables are joined based on identical column names for MyISAM tables and foreign key information for InnoDB tables."

So, your column names are not identical in both tables, and you do not use InnoDB, so foreign keys are not supported really. That is why you simply cannot use that "drag and drop" method. This is documented.