SQL query to return 1 record from multiple similar records

[Wildster]

Hi,

I’ve setup a table which contains records with multiple duplicated time fields (tblTime_Slots)
i.e.
ID Time_Slot
1 08:00
2 08:00
3 09:00
4 09:00
etc…

I want to bring back the Time_Slots in a combo box, but rather than bringing back each Time_slot twice I want to write an SQL query that will only display each Time_Slot once.
i.e.
ID Time_Slot
1 08:00
3 09:00

Therefore if the user allocates 08:00 to a booking then the next time the come to allocate a time to a new booking it returns the following in the combo box: -

ID Time_Slot
2 08:00
3 09:00

With the 08:00 Time_Slot now showing the record with ID 2 rather than ID 1 (which has been allocated to the previous booking).

If the time slot 08:00 with ID 2 was to then be allocated to a booking then the next time the query was run then the combo box wouldn’t show a 08:00 Time_Slot as they have all been allocated to previous bookings.

I’ve tried writing the following query: -

SELECT tbltime.ID, tbltime.Time
FROM tbltime
WHERE tbltime.Time in(SELECT DISTINCT tbltime.Time
FROM tblTime
GROUP BY tbltime.Time
HAVING count(*) <=2);
 

But it simply returns all the Time_Slots even the duplicated ones twice

Could the answer be to instead possibly return the MIN ID for the DISTINCT Time_Slots? Therefore for the 08:00 Time_Slot it would first of all return ID 1, then after that had been allocated to a booking it would return ID 2 for the 08:00 Time_Slot, then after that has been allocated to a booking it wouldn’t show a 08:00 Time_Slot.

Any help with this would be much appreciated as I’ve spent ages trying to figure it out. I appreciate that the query is in two parts, the first which displays only one of each of the repeated Time_Slot, and the next part of the query only returning Time_Slots that haven’t previously been allocated to a booking. I’m having that much trouble with the first part of the query I haven’t even got onto attempting the second part of the query.

Thanks

[JustJim]

Hi Wildster,
You have got yourself into a tangle haven't you? The first warning bell rings as soon as you say that you have multiple duplicated data in a table. This is a sure indication that your database is not close to being 'Normalized'. I suggest that you take a step back for a moment (it will save you a lot of time in the future) and have a read of Mary's bestselling, classic article here . I know that it is quite involved, but have a go and see how you get on.

We're willing to help, but it's a lot easier if we're working from a solid foundation.

Jim

[NeoPa]

You are going to have a lot of trouble with this one as you don't seem to have the question sorted out properly in your mind yet.

What you are ACTUALLY looking for (if my mind-reading hat is working properly) is to show ALL time slots which are not fully used up, and with each slot you want to show the first available booking. There are two bookings (numbered 1 & 2) per time slot.

If this is a fair representation of what you really want and you would like some help getting there then let us know.

[JustJim]

No, no; there's no need for thanks. It's a pleasure to help you.

Jim

[NeoPa]

I'm afraid that this is something you'll have to get used to Jim when working with the general public (and even members of Bytes as that is basically open to all anyway).

Although a good proportion are well mannered and may even be suitable to date our daughters, a sizeable minority have never been exposed to good manners for various reasons.

At the end of the day, we simply have to deal with them from time-to-time, but they are stuck with the situation (and the results thereof) for their whole lives (or until they decide to change).

This may be little consolation, but at least we know they suffer from it much more than we ever will.

PS. It's good to see you're still sticking around :)

[JustJim]

Hi NeoPa

I'm not losing any sleep over it, I was just cleaning up my subscriptions page and thought I'd give some of them a last chance before I deleted them.

Jim

[NeoPa]

Clean up subscriptions! What an idea!

I have about 4,000, and know the most recent posts are always on the front page (I make sure to include 100 per page mind you). I think they drop off automatically after a certain period anyway (Check account options for details).

[JustJim]

Clean up subscriptions! What an idea!

I have about 4,000, and know the most recent posts are always on the front page (I make sure to include 100 per page mind you). I think they drop off automatically after a certain period anyway (Check account options for details).

Yeah mate but not all of us do nearly 16 posts per day! For me housekeeping is a little feather duster around the edges (with the aforementioned grumpy sniping), for you it must involve a bulldozer!

You do great work though - keep it up.

Jim
ETA Wildster, come back - we can help!