• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

MySQL Query Syntax

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
lvollmer
Asked:
lvollmer
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
lvollmerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
lvollmerAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now