MySQL query help

Hi All,

I have the following select statement;

SELECT * FROM menu WHERE ingredients LIKE '%bananas%'

Open in new window

which finds all the menu items with bananas in them.

How do I replace '%bananas%' with a select statement that returns multiple rows?  e.g.

SELECT food_name FROM foods_i_like WHERE matches user_id=1

Open in new window


I've tried this without any luck
SELECT * FROM menu WHERE ingredients LIKE (SELECT food_name FROM foods_i_like WHERE matches user_id=1)

Open in new window


Many thanks
LVL 2
detox1978Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nathan RileyFounderCommented:
Not clear on what you're trying to accomplish.  Could you show a sample data set of what you would like returned with the names of columns?
0
Nathan RileyFounderCommented:
Ah I think you meant you wanted this:

SELECT food_name FROM foods_i_like WHERE user_id=1

Open in new window

0
Randy PooleCommented:
SELECT menu.* FROM menu M left join foods_i_like F on
M.ingrediants LIKE '%'+F.food_name+'%' where F.user_id=1

Open in new window


is this what you want?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

detox1978Author Commented:
Sorry made a typo in the second code snippet, the word matches shouldnt be in there.

Just to clarify I have two tables.

menu, which a field called called ingredients.  Sample record = "milk, bananas, nuts".

foods_i_like, which has a field called food_name. .  Sample record = "bananas"

So if I want to find all the menu items with bananas in the ingredients list I would use

SELECT * FROM menu WHERE ingredients LIKE '%bananas%'

Open in new window


I want to get all the menu items for all the foods the user likes.

The following works if only one record is returned from food_name.

SELECT * FROM menu WHERE ingredients LIKE (SELECT food_name FROM foods_i_like WHERE user_id=1)

Open in new window


How can I get it to return all the matches?
0
Nathan RileyFounderCommented:
I don't know your field names but I think you're going to need to join the tables.

select *
from menu m
inner join ingredients i on m.ingredientsid = i.id
inner join foods_i_like fil on i.ingredient = fil.ingredient
where user_id=1

Open in new window

0
detox1978Author Commented:
Oh, this will be more difficult than I thought.

Here's the actual MySQL statements

Gets a list of ID's
SELECT movies.imdbID AS MyLink FROM user_likes 
INNER JOIN movies ON movies.id = user_likes.id
WHERE user_id=1 AND rating=5 

Open in new window


The above ID's need to be JOINED with a wildcard on each side as below
SELECT * FROM imdb_likes WHERE matches LIKE '%MyLink%'

Open in new window

0
Ray PaseurCommented:
Maybe try something like this...

SELECT movies.imdbID AS MyLink FROM user_likes
INNER JOIN movies ON movies.id = user_likes.id
WHERE user_id=1 AND rating=5 AND matches LIKE '%MyLink%'

The SSCCE for this question would contain the CREATE TABLE statements for these tables and a brief script to load the test data into the tables.   Once we had that we could experiment with the query to get it just right.
0
detox1978Author Commented:
%MyLink% in the second query needs to be replaced with the results of the first query
0
Ray PaseurCommented:
Can you please show us the SSCCE?  Thanks.
0
detox1978Author Commented:
ignore my real MySQL code above as the demo data below is for the original question.

Database with sample data
/*
SQLyog Ultimate v9.63 
MySQL - 5.1.73 : Database - EE
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`EE` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `EE`;

/*Table structure for table `food_i_like` */

DROP TABLE IF EXISTS `food_i_like`;

CREATE TABLE `food_i_like` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `user_id` int(3) DEFAULT NULL,
  `menu_items_id` varchar(35) DEFAULT NULL,
  `rating` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

/*Data for the table `food_i_like` */

insert  into `food_i_like`(`id`,`user_id`,`menu_items_id`,`rating`) values (1,1,'5',5),(2,1,'6',4),(3,2,'3',4),(4,2,'1',5),(5,1,'1',5);

/*Table structure for table `menu` */

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) DEFAULT NULL,
  `ingredients` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Data for the table `menu` */

insert  into `menu`(`id`,`title`,`ingredients`) values (1,'Banana Pie','banana, pie, custard'),(2,'Apple Pie','apples, pie, custard'),(3,'Jam Tart','strawberry, jam'),(4,'Banoffee pie','banana, pie');

/*Table structure for table `menu_items` */

DROP TABLE IF EXISTS `menu_items`;

CREATE TABLE `menu_items` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `food_names` varchar(35) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `menu_items` */

insert  into `menu_items`(`id`,`food_names`) values (1,'custard'),(2,'apples'),(3,'strawberry'),(4,'jam'),(5,'banana'),(6,'pie');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Open in new window


What I want to do is display all the menu item for user_id=1 where they have given a rating=5

I can get the items the user rates as a 5 using the following MySQL
SELECT menu_items.food_names FROM food_i_like
INNER JOIN menu_items ON menu_items.id = food_i_like.menu_items_id
WHERE user_id=1 AND rating=5

Open in new window


I can see user_id 1 likes banana and custard.

In the MySQL below I can find all the menu items

banana
SELECT * FROM menu WHERE ingredients LIKE '%banana%'

Open in new window


custard
SELECT * FROM menu WHERE ingredients LIKE '%custard%'

Open in new window



How can I join the MySQL together so I can get the all the items the user_id 1 rates as 5?


Many thanks
0
PortletPaulfreelancerCommented:
I think you will seriously regret storing the menu ingredients as a comma separated string of names
0
PortletPaulfreelancerCommented:
all the items the user_id 1 rates as 5?
select distinct
          fil.user_id
        , fil.rating
        , m.title
        , m.ingredients
from menu m
    inner join menu_items mi on m.ingredients like concat('%', mi.food_names, '%')
    inner join food_i_like fil on mi.id = fil.menu_items_id
where fil.user_id = 1
    and fil.rating = 5
order by
          fil.user_id
        , m.title
        , m.ingredients
;

http://sqlfiddle.com/#!9/e4472/16

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
detox1978Author Commented:
Thanks Paul that worked perfectly.

Each of the menu items has exactly 12 ingredients, so if I stored the ingredients as ingredient1, ingredient2 ... ingredients12.  How would the code look?
0
PortletPaulfreelancerCommented:
oh no, don't do it that way..

"Each of the menu items has exactly 12 ingredients" this may be true today, but at some point you will need 14. Please don't try 12 columns - shudder - that's worse than the comma separated string (although it might perform better in some cases)

While you may not believe it, the best way to store 12 ingredients is in 12 rows
foreign keys to menu and to ingredient, this table could also store things like quantity_per_serve

(nb: and you can derive a comma separated string of ingredients using group_concat if that is really wanted)

it would look like this:
| MENU_ID | INGREDIENT_ID |
|---------|---------------|
|       1 |             1 |
|       1 |             5 |
|       1 |             6 |
|       2 |             1 |
|       2 |             2 |
|       2 |             6 |
|       3 |             3 |
|       3 |             4 |
|       4 |             5 |
|       4 |             6 |


which I produced by this:

select
        m.id as menu_id
      , mi.id as ingredient_id
from menu m
inner join menu_items mi on m.ingredients like concat('%', mi.food_names, '%')
order by 1,2

Open in new window

0
Ray PaseurCommented:
This question is already closed, so I'll sign off, but you might want to make a Google search for the exact phrase, "Should I Normalize My Database" and read the very interesting arguments on both sides of the question.

Some good learning resources for PHP and MySQL are available in this article and this book.
http://www.amazon.com/PHP-MySQL-Web-Development-4th/dp/0672329166/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.