asked on
Select statement in a select statement
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
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
MySQL Server
Last Comment
awking00
See attached.
buy-sell-count.txt
buy-sell-count.txt
Kdo has hit the nail on the head.
A couple of typos in the syntax I think, i've made a few additions to his code.
A couple of typos in the syntax I think, i've made a few additions to his code.
SELECT info . * , buyereval.BuyerCount, sellereval.SellerCount
FROM usersinfo info
LEFT JOIN (
SELECT SellerId, count( * ) AS BuyerCount
FROM user_evaluation WHERE BuyerEvaluation =3
GROUP BY SellerId
)buyereval ON info.id = buyereval.SellerId
LEFT JOIN (
SELECT SellerId, count( * ) AS SellerCount
FROM user_evaluation WHERE Seller_Evaluation = 3
GROUP BY SellerID
)sellereval ON info.id = sellereval.SellerId;
Sorry, I left out the from statement.
buy-sell-count.txt
buy-sell-count.txt
ASKER
@Kdo
Very nice, although it is does have some performance problems. However when i try to create a view with this select statement, i get an error message that tells me that the View's select contains a subquery... and as stated in my original post, I wanted to create a view.
/Rune
Very nice, although it is does have some performance problems. However when i try to create a view with this select statement, i get an error message that tells me that the View's select contains a subquery... and as stated in my original post, I wanted to create a view.
/Rune
Hi ifp,
Isn't it amazing how you can type something that's second nature to you, proof read it, and see what you're thinking instead of what's in front of you?
Kent
Isn't it amazing how you can type something that's second nature to you, proof read it, and see what you're thinking instead of what's in front of you?
Kent
RunePerstrup,
Do you get the same error with my query when using it to create a view?
Do you get the same error with my query when using it to create a view?
ASKER
Yes. I get the followin error:
Script line: 1 View's SELECT contains a subquery in the FROM clause
when I try to run the following:
CREATE VIEW `trendsalesdk_RuneTest`.`u
SELECT info . * , buyereval.BuyerCount, sellereval.SellerCount
FROM userinfo info
LEFT JOIN (
SELECT SellerId, count( * ) AS BuyerCount
FROM user_evaluation WHERE Buyer_Evaluation =3
GROUP BY SellerId
)buyereval ON info.id = buyereval.SellerId
LEFT JOIN (
SELECT SellerId, count( * ) AS SellerCount
FROM user_evaluation WHERE Seller_Evaluation = 3
GROUP BY SellerID
)sellereval ON info.id = sellereval.SellerId;
Script line: 1 View's SELECT contains a subquery in the FROM clause
when I try to run the following:
CREATE VIEW `trendsalesdk_RuneTest`.`u
SELECT info . * , buyereval.BuyerCount, sellereval.SellerCount
FROM userinfo info
LEFT JOIN (
SELECT SellerId, count( * ) AS BuyerCount
FROM user_evaluation WHERE Buyer_Evaluation =3
GROUP BY SellerId
)buyereval ON info.id = buyereval.SellerId
LEFT JOIN (
SELECT SellerId, count( * ) AS SellerCount
FROM user_evaluation WHERE Seller_Evaluation = 3
GROUP BY SellerID
)sellereval ON info.id = sellereval.SellerId;
But that's kdo's query, not the one I attached.
ASKER
@awking
I have identified a problem with your query, because you join with a userID instead of a buyerID and a sellerID. I have tried to simplify your query by just taking the buyer evaluation, and still I get the same error:
View's SELECT contains a subquery in the FROM clause.
Here is the reqritten query:
CREATE VIEW `trendsalesdk_RuneTest`.`d
select ui.*, ue.buy_count
from userinfo ui
left join
(select BuyerID, sum(case when buyer_evaluation = 3 then 1 else 0 end ) buy_count
from user_evaluation
GROUP BY BuyerID
) ue
on ui.ID = ue.BuyerID;
I have identified a problem with your query, because you join with a userID instead of a buyerID and a sellerID. I have tried to simplify your query by just taking the buyer evaluation, and still I get the same error:
View's SELECT contains a subquery in the FROM clause.
Here is the reqritten query:
CREATE VIEW `trendsalesdk_RuneTest`.`d
select ui.*, ue.buy_count
from userinfo ui
left join
(select BuyerID, sum(case when buyer_evaluation = 3 then 1 else 0 end ) buy_count
from user_evaluation
GROUP BY BuyerID
) ue
on ui.ID = ue.BuyerID;
ASKER
Can it really be true that a view cannot contain a sub select. In that case MySQL i a lot weaker than I originally assumed...
Unfortunately, I think that is the case. See this link.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Will try to see if a different approach is available.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Will try to see if a different approach is available.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Glad to have, at least, provided some inspiration :-)
All that's really necessary is to determine the join key. Even though it's not mentioned, I'm assuming that there is a userID in the user_evaluation table.
So now we count and join. :)
The code below is perhaps the most straight forward, but it can also be written "in line".
Good Luck,
Kent
Open in new window