Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL Query Syntax

Posted on 2008-06-18
4
617 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 143

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 143

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

860 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