Need MySQL Query Guru (intersection of record data)

Question

Say i have a table as shown:

id, auctionUser, auctionId, MinPrice, NumBids, PlacedBids

And then say i've got the following entries in above table that have the same auctionId:

1 | user1 | 99 | 10.25 | 20 | 0
2 | user2 | 99 | 10.50 | 50 | 0

Is there a way to write a query ( WHERE auctionId = 99 ) that would return a row for every 0.01 of MinPrice where the two rows would 'intersect' (don't know if that's the right word but it's the best i could come up with to describe it) based on the number of bids in NumBids? So for the data above, there would be an 'intersect' of the two users from 10.50 thru 10.75. I'd like to be able to create the flowing data to display like so , alternating bids between the users for the number of bids set in NumBids:

(bidAmount) | (auctionUser) | NumBids | PlacedBids
10.50 | user2 | 50 | 1
10.51 | user1 | 20 | 1
10.52 | user2 | 50 | 2
10.53 | user1 | 20 | 2
10.54 | user2 | 50 | 3
10.55 | user1 | 20 | 3
.
.
.
10.70 | user2 | 50 | 20
10.71 | user1 | 20 | 20 <-- ends here for user1 since 20 NumBids would be used up
10.72 | user2 | 50 | 21

I don't even know if this is possible via a sql query or not -- or even how to start such a query. I thought i'd throw it out there to see if any sql guru's had and idea. I figured if there was a way to do it, it would probably be much faster to produce it from a query that trying to use php to cycle through and produce the result...maybe not though.

As always, MUCHO THANKS for any time and advice you can spare on this!

Answer 1

I can't say I understand exactly what you want, but I think you need to generate rows. One way of generating rows is to use a Numbers table, which is basically a table of consecutive integers.

Have a look at my answer to this question. It is not related to your question, but there is code to generate such a numbers table.

So if you want to generate 1 row for each 0,01 difference, you would calculate nr of cents (or whatever the currency was) and join to the numbers table with a filter on n < nr_of_cents.

Edit: Ok, I'll try. First, some sample data.

create table auctions(
   auctionuser  int 
  ,auctionid    int
  ,minprice     decimal(5,2)
  ,numbids      int
);

insert into auctions values(1, 1, 2.20, 2);
insert into auctions values(2, 1, 3.30, 4);
insert into auctions values(3, 1, 4.40, 6);

select *
  from auctions
 where auctionid = 1;

+-------------+-----------+----------+---------+
| auctionuser | auctionid | minprice | numbids |
+-------------+-----------+----------+---------+
|           1 |         1 |     2.20 |       2 |
|           2 |         1 |     3.30 |       4 |
|           3 |         1 |     4.40 |       6 |
+-------------+-----------+----------+---------+
3 rows in set (0.00 sec)

I think the following is close to what you want. Note that I have used the numbers table in the post I linked to.

select a.auctionuser
      ,n as user_bid
      ,minprice
      ,numbids
      ,a.minprice + (0.01 * (n-1)) as bid
  from auctions a
      ,numbers
 where numbers.n <= a.numbids
   and a.auctionid = 1
 order 
    by n
      ,a.minprice
      ,a.auctionuser;


+-------------+----------+----------+---------+------+
| auctionuser | user_bid | minprice | numbids | bid  |
+-------------+----------+----------+---------+------+
|           1 |        1 |     2.20 |       2 | 2.20 |
|           2 |        1 |     3.30 |       4 | 3.30 |
|           3 |        1 |     4.40 |       6 | 4.40 |
|           1 |        2 |     2.20 |       2 | 2.21 |
|           2 |        2 |     3.30 |       4 | 3.31 |
|           3 |        2 |     4.40 |       6 | 4.41 |
|           2 |        3 |     3.30 |       4 | 3.32 |
|           3 |        3 |     4.40 |       6 | 4.42 |
|           2 |        4 |     3.30 |       4 | 3.33 |
|           3 |        4 |     4.40 |       6 | 4.43 |
|           3 |        5 |     4.40 |       6 | 4.44 |
|           3 |        6 |     4.40 |       6 | 4.45 |
+-------------+----------+----------+---------+------+
12 rows in set (0.00 sec)