select query + mysql

Could you please help me to find out how to select records from a table by the value of another table.

for example

I have a user table with fields - userId,username,phone,suburb,city
Another table 'review' having fields - userId,recommendedtoafriend.

One entry in the user table may have multiple entry in the review.

userId userName userId recommentedtoafriend
1 a1 1 yes
2 a2 1 yes
3 a3 2 No
1 Yes

I have to populate all the records from the user table with the priority of recommentedtoafriend. List with recommentedtoafriend 'yes' come to the top and then rest underneath.

Anybody can help me to sort out this..Please...

You need to use group by command with count to get the total 'yes' of any userid. In both tables we can use userid to connect both tables. For all records of first table at least one record exist in second table so we can use where clause, otherwise we have to use Join . First with where clause.
Select table1.username, table1.userid, count(table2.some_unique_field ) as t_no from table1,table2 where table1.userid=table2.userid group by table2.userid order by t_no desc

You also can read order by to display records from highest number of 'Yes' to lowest number.
Please Login to post your reply or start a new topic