troubleshooting Question

Select statement in a select statement

Avatar of RunePerstrup
RunePerstrup asked on
MySQL Server
14 Comments1 Solution322 ViewsLast Modified:
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.


/Rune
ASKER CERTIFIED SOLUTION
RunePerstrup

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros