I have two tables - usersinfo table and a user_evaluation table. The usersinfo table has standard user info (name, address etc). The user_evaluation table has the following fields: SellerID, Seller_Evaluation, BuyerID and a BuyerEvaluation. SellerEvaluation and BuyerEvaluation can be set to a number from 1 to 3
I want to create a view that retrieves all the collums from the usersinfo table and has two additional collumns added.
The first collumn should retrieve the number of records that have the value "3" in the SellerEvaluation field (from the user_evaluation table) - and the second collumn should retrieve the number of records that have the value "3" in the BuyerEvaluationField.
Can anyone suggest a query that does this efficiently? I currently use MySQL - but if you have a sollution in another SQL dialect, I should probably be able to translate it.
The query will be used in a view - so I suppose that declaring temporary tables is not an option - unfortunately.