nomadsoul Posted June 10, 2011 Share Posted June 10, 2011 Hi, On page 166 it say the non_coffee_products table has a many-to-one relation with non_coffee_categories table. I'm guessing this is the FK in the non_coffee_products table: KEY `non_coffee_category_id` (`non_coffee_category_id`) ? But I see no relation to this table: CREATE TABLE `non_coffee_categories` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext NOT NULL, `image` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Almost all my SQL experience is in T-SQL so maybe I just don't understand MySQL syntax. I'm probably missing the obvious (as usual with me) and pre-apologize if I am. Thanks in advance for your help. Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 10, 2011 Share Posted June 10, 2011 Almost all my SQL experience is in T-SQL so maybe I just don't understand MySQL syntax. I'm probably missing the obvious (as usual with me) and pre-apologize if I am. Thanks in advance for your help. Hey, I'm not sure how T-SQL works. In MySqli, we don't define foreign keys in the table structure - we do it in our queries. I don't know how the structure for table "non_coffee_products" is. The columns in the SELECT statement is just made up by me. SELECT product.id, product.name, category.name FROM non_coffee_products AS product INNER JOIN non_coffee_categories AS category ON (product.category_id = category.id) I don't know if T-SQL has Inner Joins, but I'm guessing they do. Hope it makes sense. Several FROM tables and a WHERE clause might also work if you don't need category names etc. 1 Link to comment Share on other sites More sharing options...
Larry Posted June 10, 2011 Share Posted June 10, 2011 "KEY" is just another term for INDEX, so the CREATE statement creates an index on that column, as it's a foreign key to the other table. The purpose is to improve the performance of those JOINS. You can, by the way, define foreign key constraints in the table structure, depending upon the version of MySQL in use, and if using the InnoDB storage engine. Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 10, 2011 Share Posted June 10, 2011 Did not know that, Larry. Will check it out. Link to comment Share on other sites More sharing options...
nomadsoul Posted June 11, 2011 Author Share Posted June 11, 2011 Thanks Larry and Antonio, that cleared it up nicely. So I'm guessing that inner joins were used a lot before MySQL became relational? I heard that MySQL was not always relational. Link to comment Share on other sites More sharing options...
Larry Posted June 11, 2011 Share Posted June 11, 2011 I've been familiar with MySQL ever since it was lumped in with mSQL, and I've never heard of it not being relational. Also, joins are always for relational databases, regardless of the join type. Link to comment Share on other sites More sharing options...
Recommended Posts