Larry Vollmer
asked on
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?
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Open in new window