Quick SQL help!

RevillWebDesign posted this at 09:47 — 15th June 2011.

Joined: Sep 2010

Hi All,

Need some quick SQL help. Details are as follows:

I have 4 tables:

businesses [Holds business info]
loc_link [links businesses to a location]
cat_link [links businesses to a category]
reviews [holds reviews for businesses]

I have a query:

SELECT * FROM businesses, loc_link, cat_link WHERE loc_link.loc_id = '$loc_id' AND loc_link.business_id = businesses.id AND cat_link.cat_id = '$cat_id' AND cat_link.business_id = businesses.id AND businesses.active = 'true'

What I need to do on the fly is check how many reviews the businesses have and then ORDER BY amount of reviews.

Is it possible to do something like mysql_num_rows() within an SQL query?

Any help would be greatly appreciated.

Thanks.

Revil Web Design

sara posted this at 11:08 — 15th June 2011.

They have: 1 posts

Joined: Jun 2011

did the table show the reviews of the business and order by amount? because, if the question want to check the reviews, it can be to see on the table..and the answer is on the table..

RevillWebDesign posted this at 13:20 — 15th June 2011.

He has: 16 posts

Joined: Sep 2010

I don't really understand what you are asking? I need to check how many reviews each business has within that SQL query and then order them by the amount of reviews they have so,

if business 1 has 3 reviews and business 2 has 1, then business 1 should be displayed 1st.

I know this can be done easily with mysql_num_rows() but I need to do it on the fly within the SQL query if possible, unless someone can come up with another idea as to how it can be done.

Cheers,

Leon.

Revil Web Design

Greg K posted this at 17:14 — 15th June 2011.

He has: 2,145 posts

Joined: Nov 2003

Just woke up so mind is still waking up, but give this a try:

SELECT *, (SELECT COUNT(review_id) FROM reviews AS r WHERE r.business_id = b.businesses.id) AS review_count
FROM businesses AS b
JOIN loc_link AS ll ON ll.business_id = b.businesses.id 
JOIN cat_link AS cl ON cl.business_id = b.businesses.id 
WHERE ll.loc_id = '$loc_id' AND cat_link.cat_id = '$cat_id' AND businesses.active = 'true'
ORDER BY review_count

RevillWebDesign posted this at 10:30 — 29th June 2011.

He has: 16 posts

Joined: Sep 2010

Thanks for your help! It nearly works, it doesn't understand the first b.businesses column, this is a little out of my league so if you could help that would be highly appreciated!

Thank you!

lrevillhendley.

Revil Web Design