SQL Query with an Array

Question

I am trying to write a query for my database.

Basically, I have an array of strings containing room numbers that I need to match up with another database containing events in the rooms. I want to find the next event happening in that room. Ex: I have an array room containing [ZACH102, ZACH103]. I want to find the database to return the next event happening in ZACH102 and ZACH103. I know how to find events for 1 room using the query below. But how do I do this using an array?

select   name,
         eventtime,
         (eventtime - currenttime) > 0 as time_from_test
from     the_table
where    the_table.room = ZACH102
order by time_from_test
limit 1

Thanks in advance!

EDIT: Sorry if my explanation wasnt clear enough, but I only want the earliest event in each room in the array. Note the limit 1 at the end.

EDIT: Yes, I put the limit 1 there to mean that I only want 1 event from ZACH 102 and then i want 1 event from ZACH 103. I explained that the code above only finds events for 1 room.

How are you passing the array? Is it a SQL array or are you using a different language then calling the function? — Gage
– Gage, Commented Mar 16, 2012 at 18:21
I have a result from a database query coming in where I parse the information in php and put the names of the rooms into an array. Then I want to use that array for the query above — maknelly
– maknelly, Commented Mar 16, 2012 at 18:24
Also it time_from_test necessary or would you just be looking for the next event from the current system time? — Gage
– Gage, Commented Mar 16, 2012 at 19:54
Yes, time_from_test would be necessary because I only want events that are going to happen and not ones that have happened. I keep my time in minutes since a certain date. So, if the minutes are less than the current date converted into minutes then I dont want to pick it up. — maknelly
– maknelly, Commented Mar 16, 2012 at 20:36

Joel Coehoorn · Accepted Answer · 2012-03-16 20:53:15Z

In Sql Server I'd use a common table expression. But since it looks like you're using mysql, I'd do something like this:

select t2.name, t2.room, t2.eventtime, t2.time_from_test
from the_table t1
inner join
(
    select   name, room,
         min(eventtime) as eventtime,
         (eventtime - currenttime) > 0 as time_from_test
    from     the_table
    where    the_table.room in ('ZACH102', 'ZACH103')
    group by name, room
    order by time_from_test
) t2 on t2.room = t1.room and t2.name = t1.name

Eventually you're gonna need to set this up dynamically, where you don't know the contents of your IN (...) clause in advance. When that time comes, the best thing to do is plan ahead so that your array is already stored in a table somewhere. That will give you two options. The first is to nest a sub query in your IN (...) condition:

select t2.name, t2.room, t2.eventtime, t2.time_from_test
from the_table t1
inner join
(
    select   name, room,
         min(eventtime) as eventtime,
         (eventtime - currenttime) > 0 as time_from_test
    from     the_table
    where    the_table.room in (select room from shopping_cart where session_key='MySessionKey')
    group by name, room
    order by time_from_test
) t2 on t2.room = t1.room and t2.name = t1.name

The other is to use a join:

select t2.name, t2.room, t2.eventtime, t2.time_from_test
from the_table t1
inner join
(
    select   name, room,
         min(eventtime) as eventtime,
         (eventtime - currenttime) > 0 as time_from_test
    from     the_table
    inner join shopping_cart on shopping_cart.room = the_table.room
    where shopping_cart.session_key = 'MySessionKey'
    group by name, room
    order by time_from_test
) t2 on t2.room = t1.room and t2.name = t1.name

mechanical_meat · Accepted Answer · 2012-03-16 18:14:19Z

1

where    the_table.room in ('ZACH102', 'ZACH103')

edited Mar 16, 2012 at 18:14

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

answered Mar 16, 2012 at 18:13

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

3 Comments

maknelly Over a year ago

so this will return me 2 events, where 1 of them is the earliest event in zach102 and the earliest event in zach 103? also if the name of my array is $rooms do i just do where the_table.room in $rooms?

Gage Over a year ago

Using that where clause basically means that the query will return all records with a room of ZACH102 and ZACH103. Then your order by would order all the records by time. So it would only select the top time for one of the rooms. Are you looking for the top time in each room?

maknelly Over a year ago

Yes sir, im looking for the top time in each room.

Collectives™ on Stack Overflow

SQL Query with an Array

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related