• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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