RE: [PHP-DB] Delicious style Tags table

If there is only one search term - "soup" your where statement would be: Where name like 'soup' But you need two matches, and the terms are "soup", "vegetarian". Try: Where GROUP_CONCAT('name') like 'soup' AND GROUP_CONCAT('name') like 'vegetarian' group by taggings.id You probably awnt a LEFT JOIN instead of a Simcha Younger -----Original Message----- From: Catharsis [mailto:chris.lock@thisisbd.com] Sent: Thursday, September 11, 2008 1:41 PM To: php-db@lists.php.net Subject: [PHP-DB] Delicious style Tags table So I am having difficulty thinking of how to make this select query. I have two tables that mimic tags similar to flickr, delicious etc. They are defined below CREATE TABLE IF NOT EXISTS taggings ( id int(11) unsigned NOT NULL auto_increment, tag_id int(11) NOT NULL, taggable_id int(11) NOT NULL, taggable_type varchar(255) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (id), UNIQUE KEY index_taggings_on_tag_id_and_taggable_id_and_taggable_type (tag_id,taggable_id,taggable_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE IF NOT EXISTS tags ( id int(11) NOT NULL auto_increment, name varchar(255) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (id), UNIQUE KEY unique_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Just to explain the taggings table, tag_id points directly to the tag table, taggable_id is the id of the item you have tagged in another table. The taggable_type is string reference to the table that the item you tagged sits in, so in the exaplme below it would be a table called 'recipes' So, say you have 2 items each with the same tag but one item as two tags. For instance a two soup Recipes could be tagged with 'soup' but only one of them is vegetarian. So Recipe 1 has the tag 'soup' and recipe 2 has 'soup' and 'vegetarian' I want to be able to pass my SQL the word 'soup' and it return both records in taggings table which will point to both recipes. However if I want just vegetarian soups then I only want it to return the one record. I hope that is understandable What I have currently (below) is just a simple join. Which obviously doesn't work. I just cant think how to piece these two tables together to get the records I want. SELECT tags.*, taggings.* FROM tags JOIN taggings ON tags.id = taggings.tag_id WHERE (name IN ('vegetarian', 'soup')) -- View this message in context: http://www.nabble.com/Delicious-style-Tags-table-tp19433010p19433010.html Sent from the Php - Database mailing list archive at Nabble.com. No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.20/1666 - Release Date: 11/09/2008 07:03