Help with next position

[EchoFool]

Hey

 

Im trying to use a query to check which slot of 8 (1 -8 ) is occupied from the person who is in a slot number.

 

Say i was in slot 2 and slot 3 and 4 were empty then the next used slot is 5.

 

This i find easy in MYSQL how ever - when some one is say in slot 7 and slot 8 is free it then needs to go back to and check slot 1 and continue in ascending order from that.

 

Imagine a circle with 8 points equally spaced out so it would be 1 through to 8 then 1 and so on.

 

How would i achieve this added complicated part?

[awjudd]

The actual query really depends on your implementation ... however, something like this may work:

 

SELECT slot AS SlotRank, slot FROM slots WHERE slot > :current_slot ORDER BY slot 
UNION
SELECT slot + 8 AS SlotRank, slot FROM slots WHERE slot <= :current_slot ORDER BY slot

 

Where :current_slot is the current slot that you are in.

 

First query: Grab anything that is after our current slot

Second query: Grab anything that is less than or is our current slot offsetting it by the maximum number of elements in your group (i.e. so that they have higher slot numbers.

 

That make sense?

 

~judda

[EchoFool]

Im slightly confused by the second SELECT query in the Union.

 

 

[awjudd]

What part of the query are you confused about?

 

If you post your table structure, then I may be able to make it more tuned to your actual table rather than my guessimation as to what the table may be ...

 

~judda

[EchoFool]

Heres my table:

 

UserSeats

UserID | SlotID

  2      |    4

  1      |    6

  5      |    1

  7      |    7

 

Lets argue im UserID 4 and i need to find the next used seat ( 8 is not used so we go back to 1 then 2 then 3) then finally 4 which IS used which would be the result im trying to get.

 

Hope this helps.

[awjudd]

The original query should still hold given your table structure.  With just changing the field I called slot to SlotID.

 

What was it about the select after the union that threw you for a loop?

 

~judda

[awjudd]

Just an extension on what I said.  If the current position is 8, you will get the following:

 

Query Part 1:

SELECT SlotID AS SlotRank, SlotID FROM UserSeats WHERE SlotID > 8

 

Returns an empty table because there are no seats greater than 8 (that is the maximum number of seats)

 

Query Part 2:

SELECT SlotID + 8 AS SlotRank, SlotID FROM UserSeats WHERE SlotID <= 8

 

Returns:

SlotRank | SlotID

-----------------------

      12    |    4

      14    |    6

      9      |    1

      15    |    7

 

So, when we UNION these two sets, we will get (same as the results from the second part of the query because we are at the maximum value):

 

SlotRank | SlotID

-----------------------

      12    |    4

      14    |    6

      9      |    1

      15    |    7

 

Then using that as the basis, we can order those results based on the SlotRank to see which should come first.  For example:

 

SELECT SlotID FROM (
SELECT SlotID AS SlotRank, SlotID FROM UserSeats WHERE slot > :current_slot
UNION
SELECT SlotID + 8 AS SlotRank, SlotID FROM UserSeats WHERE slot <= :current_slot )
ORDER BY SlotRank

 

This will return the following result set (given that the current slot is :

SlotID

-----------

    1

    4

    6

    7

 

Which would mean that the next used seat is seat 1 ... which satisfies your requirement?  It wouldn't choose 4 first because you said you wanted the next used seat since if we are at 8 would be 1 ...

 

Does this make sense?

 

~judda

[EchoFool]

Oh so the second query temporarily makes it 1 to 16 ?

[awjudd]

Yup, just so that we can fit all of the groups in and in the order in which they belong.

 

~judda

[EchoFool]

Thanks i will give it a try

[awjudd]

That's what I meant when I was mentioning the offset

 

Let me know how it turns out

 

~judda

[EchoFool]

Okay i tried but i get an error... heres my query:

 

  SELECT COUNT(UserID) AS Valid,SeatPosition FROM users 
   WHERE TableID='2' AND
   SeatPosition > $MyCurrentSeatPosition ORDER BY SeatPosition

   UNION

   SELECT SeatPosition + 8 AS SeatPosition FROM users WHERE TableID='2' 
   AND SeatPosition <= $MyCurrentSeatPosition ORDER BY SeatPosition LIMIT 1

 

My Error:

Incorrect usage of UNION and ORDER BY

[awjudd]

There is no need to order by the two queries.  Try a similar fashion to what I have in my third post with the union bit being within a subquery.  For example:

 

SELECT SeatPosition FROM (
SELECT SeatPosition AS SeatOffset,SeatPosition FROM users 
   WHERE TableID='2' AND
   SeatPosition > $MyCurrentSeatPosition 

   UNION

   SELECT SeatPosition + 8 AS SeatOffset, SeatPosition FROM users WHERE TableID='2' 
   AND SeatPosition <= $MyCurrentSeatPosition LIMIT 1 ) ORDER BY SeatPosition

 

The other thing I noticed in your original query is that you wrote last post, you have two things being returned, whereas the second query only returns one.  Which breaks what is required for a union (both tables must return the same number of columns).

 

~judda

[EchoFool]

Oh okay thanks for the tip i will edit it soon and let you know!

 

Thanks

[EchoFool]

Hmm now i get:

Every derived table must have its own alias

 

I set the tables then to : users t1 and users t2 but same problem exists.

 

Any ideas?

[awjudd]

After the closing bracket, you need an alias.

 

  SELECT * FROM ( ... ) a

 

That should do the trick.

 

~judda

[EchoFool]

Works a treat thanks sooo much !