[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL query help

Posted on 2014-07-14
15
Medium Priority
?
364 Views
Last Modified: 2014-07-15
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
0
Comment
Question by:detox1978
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40195445
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
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40195455
Ah I think you meant you wanted this:

SELECT food_name FROM foods_i_like WHERE user_id=1

Open in new window

0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40195479
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 Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 2

Author Comment

by:detox1978
ID: 40195492
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
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40195501
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
 
LVL 2

Author Comment

by:detox1978
ID: 40195545
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40195573
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
 
LVL 2

Author Comment

by:detox1978
ID: 40195620
%MyLink% in the second query needs to be replaced with the results of the first query
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40195652
Can you please show us the SSCCE?  Thanks.
0
 
LVL 2

Author Comment

by:detox1978
ID: 40195820
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40196067
I think you will seriously regret storing the menu ingredients as a comma separated string of names
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40196078
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
 
LVL 2

Author Comment

by:detox1978
ID: 40196363
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40196385
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40196505
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question