Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this long query which works one for processing one sale.
What I'm trying to do is loop it until no more B.boughtPending = 0 and S.soldPending = 0 and then
Select all the ones that B.boughtPending > 0 and S.soldPending > 0 and also has to match by B.itemId and S.itemId and both S.aborted=0 and B.aborted=0.

Here is the working query below.

DROP TABLE IF EXISTS sale;

CREATE TEMP TABLE sale ( 
    buyerId,
    sellerId,
    willBuyAmount 
);

INSERT INTO sale ( 
    buyerId,
    sellerId,
    willBuyAmount 
) 
SELECT B.id AS buyerId,
       S.id AS sellerId,
       CASE
            WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  ) 
            ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  ) 
       END AS willBuyAmount
  FROM Buying AS B, 
       Selling AS S
ON B.itemId = S.itemId 
AND
B.aborted = 0 
AND
S.aborted = 0 
AND
B.boughtPending = 0 
AND
S.soldPending = 0 
AND
B.price >= S.price 
AND
S.sold < S.amount 
AND
B.bought < B.amount
 LIMIT 1;

UPDATE Buying
   SET boughtPending = boughtPending +( 
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =( 
           SELECT buyerId
             FROM sale
       );

UPDATE Selling
   SET soldPending = soldPending +( 
           SELECT willBuyAmount
             FROM sale 
       )
 WHERE id =( 
           SELECT sellerId
             FROM sale
       );

DROP TABLE sale;

Here is my failed attempt. (From research I need a JOIN HERE to fix it?)

SELECT * 
FROM 
Buying AS B, 
Selling AS S
ON B.itemId = S.itemId
AND
B.aborted = 0
AND
S.aborted = 0
AND 
b.boughtPending > 0
AND
S.boughtPending > 0 
(
DROP TABLE IF EXISTS sale;

CREATE TEMP TABLE sale ( 
    buyerId,
    sellerId,
    willBuyAmount 
);

INSERT INTO sale ( 
    buyerId,
    sellerId,
    willBuyAmount 
) 
SELECT B.id AS buyerId,
       S.id AS sellerId,
       CASE
            WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  ) 
            ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  ) 
       END AS willBuyAmount
  FROM Buying AS B, 
       Selling AS S
ON B.itemId = S.itemId 
AND
B.aborted = 0 
AND
S.aborted = 0 
AND
B.boughtPending = 0 
AND
S.soldPending = 0 
AND
B.price >= S.price 
AND
S.sold < S.amount 
AND
B.bought < B.amount
 LIMIT 1;

UPDATE Buying
   SET boughtPending = boughtPending +( 
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =( 
           SELECT buyerId
             FROM sale
       );

UPDATE Selling
   SET soldPending = soldPending +( 
           SELECT willBuyAmount
             FROM sale 
       )
 WHERE id =( 
           SELECT sellerId
             FROM sale
       );

DROP TABLE sale;
)
share|improve this question
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.