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

x
?
Solved

mysql join assistance

Posted on 2011-05-04
5
Medium Priority
?
292 Views
Last Modified: 2012-05-11
Hi. I have a database with two tables
Hotels
Reviews

The structure is given below. I wanted to get the following fields as the result set from a query and I was wondering if someone could help me with the query.

For a given city, say TOKYO, I want to get
Hotel name, ranking, stars, title, text, greenDotRank, GDRFromReviews, date.

Here is the information on the tables:

Table Name: Hotel
      id       int(11)             UNSIGNED       No       None       auto_increment       Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      name       varchar(255)       utf8_general_ci             No       None             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      city       varchar(128)       utf8_general_ci             No       None             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      stars       float                   No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      ranking       int(6)             UNSIGNED       No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      url       varchar(255)       utf8_general_ci             No       None             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      last_scraper_time       int(11)                   No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      greenDotRank       float                   No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      services       text       utf8_general_ci             Yes       NULL             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      last_review_id       bigint(21)                   No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       
      
      
Table Name: Reviews

id       int(11)             UNSIGNED       No       None       auto_increment       Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      hotel_id       int(11)             UNSIGNED       No       None             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      title       varchar(255)       utf8_general_ci             No       None             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      text       text       utf8_general_ci             Yes       NULL             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      GDRFromReviews       float             UNSIGNED       No       0             Browse distinct values       Change       Drop       Primary       Unique       Index       Fulltext
      date       varchar(48)       utf8_general_ci             Yes       NULL             Browse distinct values       Change


Thank you.
Table Name: Hotel
	id 	int(11) 		UNSIGNED 	No 	None 	auto_increment 	Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	name 	varchar(255) 	utf8_general_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	city 	varchar(128) 	utf8_general_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	stars 	float 			No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	ranking 	int(6) 		UNSIGNED 	No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	url 	varchar(255) 	utf8_general_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	last_scraper_time 	int(11) 			No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	greenDotRank 	float 			No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	services 	text 	utf8_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	last_review_id 	bigint(21) 			No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	
	
	
Table Name: Reviews

id 	int(11) 		UNSIGNED 	No 	None 	auto_increment 	Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	hotel_id 	int(11) 		UNSIGNED 	No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	title 	varchar(255) 	utf8_general_ci 		No 	None 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	text 	text 	utf8_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	GDRFromReviews 	float 		UNSIGNED 	No 	0 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
	date 	varchar(48) 	utf8_general_ci 		Yes 	NULL 		Browse distinct values 	Change

Open in new window

0
Comment
Question by:onyourmark
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35689195
Select H.name, H.ranking, H.stars, R.title, R.text, H.greenDotRank, R.GDRFromReviews, R.date
From Hotel H inner join Reviews R on H.id = R.hotel_id
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35689198

for specific City

Select H.name, H.ranking, H.stars, R.title, R.text, H.greenDotRank, R.GDRFromReviews, R.date
From Hotel H inner join Reviews R on H.id = R.hotel_id
where H.city='TOKYO'
0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35689210
Try this:

SELECT * FROM Reviews A LEFT JOIN Hotel B on A.hotel_id = B.id WHERE B.name = 'TOKYO';

Open in new window

0
 
LVL 31

Expert Comment

by:farzanj
ID: 35689254
From what I see, id is the only primary key in common in both tables.

select h.name, h.ranking, h.stars, r.title, h.greenDotRank, r.GDRFromReviews, r.text, r.date
from hotel as h, reviews as r
where h.id = r.id
0
 

Author Closing Comment

by:onyourmark
ID: 35689255
THANKS.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Six Sigma Control Plans
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

873 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