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

Kent Olsen

Hi Rune,

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


SELECT info.*, buyereval.Buyer3Count, sellereval.SellerEval3Count
FROM userinfo info
LEFT JOIN
(
  SELECT user_id, count(*)
  FROM user_evaluation
  where buyer_evaluation = 3
  group by user_id
) buyereval
  ON info.user_id = buyereval.user_id
LEFT JOIN
(
  SELECT user_id, count(*)
  FROM user_evalution
  where seller_evalution = 3
  group by user_id
) sellereval
  ON info.user_id = sellereval.user_id;

Open in new window

awking00

See attached.
buy-sell-count.txt

ifp_support

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.

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;

Open in new window

awking00

Sorry, I left out the from statement.
buy-sell-count.txt

RunePerstrup

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

Kent Olsen

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

awking00

RunePerstrup,
Do you get the same error with my query when using it to create a view?

RunePerstrup

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`.`usersinfo_testView` AS
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;

awking00

But that's kdo's query, not the one I attached.

RunePerstrup

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`.`deleteme` AS
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;

RunePerstrup

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...

awking00

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.

ASKER CERTIFIED SOLUTION

RunePerstrup

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

awking00

Glad to have, at least, provided some inspiration :-)