Solved

MySQL Query Syntax

Posted on 2008-06-18
4
615 Views
Last Modified: 2013-12-12
I have the following query:

SELECT a.venuename, a.venueid, b.stars, b.reviewtext, AVG( stars ) AS average, sum(b.total) AS total
FROM venue a, venuereview b
WHERE a.venueid = b.venueid
AND b.id = 50
AND b.switch = '1'
GROUP BY b.venueid
ORDER BY average DESC


What I want to do is display the review with the ID of 50 first, then display the rest of the reviews for the venueid related to the id - 50.

Can anyone help me out?
0
Comment
Question by:lvollmer
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21815562
if I understand correctly:
SELECT a.venuename, a.venueid, b.stars, b.reviewtext, b.id, AVG( stars ) AS average, sum(b.total) AS total
FROM venue a, venuereview b
WHERE a.venueid = b.venueid
AND b.switch = '1'
GROUP BY b.venueid
ORDER BY CASE WHEN b.id = 50 THEN 0 ELSE 1 END ASC, average DESC

Open in new window

0
 

Author Comment

by:lvollmer
ID: 21815744
Angel - that gave me all reviews.

here is an example of what I need.

reviewID-50 has a venueID of 900

venueID900 has 4 reviews

I want to display those 4 reviews, with the first review displaying reviewID-50
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21815775
so, when a VenueID has at least 1 reviewID = 50, then show them all for that VenueID
SELECT a.venuename, a.venueid, b.stars, b.reviewtext, b.id, AVG( stars ) AS average, sum(b.total) AS total
FROM venue a
JOIN venuereview b
  ON a.venueid = b.venueid
 AND b.switch = '1'
WHERE exists ( SELECT NULL 
                 FROM venuereview i
                WHERE a.venueid = i.venueid
                  AND i.id = 50
             )
GROUP BY b.venueid
ORDER BY CASE WHEN b.id = 50 THEN 0 ELSE 1 END ASC, average DESC

Open in new window

0
 

Author Comment

by:lvollmer
ID: 21815877
slight modification and it works - thanks!

SELECT a.venuename, a.venueid, b.stars, b.reviewtext, b.id, AVG( stars ) AS average, sum( b.total ) AS total
FROM venue a
JOIN venuereview b ON a.venueid = b.venueid
AND b.switch = '1'
WHERE EXISTS (

SELECT NULL
FROM venuereview i
WHERE a.venueid = i.venueid
AND i.id =49
)
GROUP BY b.id
ORDER BY CASE WHEN b.id =49
THEN 0
ELSE 1
END ASC , average DESC
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 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