StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComplex MySQL Query 1-N Relationship
text
Body
copied!<p>I'm currently designing a database (MySQL) for drink recipes and want users to be able to enter what ingredients they have and I will give them all recipes that include <strong>only</strong> those ingredients. What I need is a query where I can select all drinks from table <code>drinks</code> where the only ingredients are <strong>like</strong> (<em>the reason for this is someone may enter vodka or smirnoff vodka but I will treat them as the same thing</em>) the ingredients in the table <code>user_ingredients</code></p> <p>Here are the three tables that will be involved in this query:</p> <h3>drinks</h3> <pre><code>CREATE TABLE `drinks` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `drink` varchar(64) NOT NULL DEFAULT '', `glass` int(11) unsigned NOT NULL, `instructions` text, PRIMARY KEY (`id`), UNIQUE KEY `un_drink` (`drink`), KEY `in_id` (`id`), KEY `fk_glass` (`glass`), CONSTRAINT `fk_glass` FOREIGN KEY (`glass`) REFERENCES `glasses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=11946 DEFAULT CHARSET=utf8; </code></pre> <h3>recipe_ingredients</h3> <pre><code>CREATE TABLE `drink_ingredients` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `drink` int(11) unsigned NOT NULL, `ingredient` int(11) unsigned NOT NULL, `amount` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `in_drink` (`drink`), KEY `in_ingredient` (`ingredient`), KEY `fk_amount` (`amount`), CONSTRAINT `fk_amount` FOREIGN KEY (`amount`) REFERENCES `amounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_drink` FOREIGN KEY (`drink`) REFERENCES `drinks` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_ingredient` FOREIGN KEY (`ingredient`) REFERENCES `ingredients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=46026 DEFAULT CHARSET=utf8; </code></pre> <h3>user_ingredients</h3> <pre><code>CREATE TABLE `user_ingredients` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user` int(11) unsigned DEFAULT NULL, `ingredient` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_user_ingredient` (`ingredient`), CONSTRAINT `fk_user_ingredient` FOREIGN KEY (`ingredient`) REFERENCES `ingredients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; </code></pre> <p>Thank you, I've been stuck on this for awhile. -Stefan</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload