How do I show only one record - the record with smallest value?

Question

I hope the title of this thread makes sense.

Please take a look at the screenshot below.

enter image description here

Top half shows the db with two users who have signed up for training.

By default, there is a 45 seat capacity

After userB signs up, there are 44 left.
Then userA signs up, now there are 43 seats available.

So far so good.

But please take a look at the bottom half of that screenshot. Notice that the screen shows 43 Seats available as well as 44 seats available. This is very confusing to our users. How can we just show the least available number? In other words, after each sign up, display only the number representing the remaining seat. In the example above, it will be 43.

Next time another user signs up, the number decreases by 1 and becomes 42 and that's the number we would like to show per the location where the user signed up for that class and for the date of the class.

Below is the code that I am using.

How can I tweak it to show just the most current number which is the smallest of the number of availableSeats?

<asp:SqlDataSource ID="sqlDataSourceloc" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>" 
    SelectCommand="SELECT locationId, Location FROM tblLocations order by location asc"></asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DBConnectionString %>" 
 SelectCommand="select DISTINCT l.LocationId,c.courseId, c.coursename, (case when isnull(t.availableSeats,30) = 0 then 'Class is full' 
                else  str(isnull(t.availableSeats,30)) end) availableSeats,d.dateid,d.trainingDates, d.trainingtime, c.CourseDescription,i.instructorName, l.location,l.seating_capacity 
                from tblLocations l
                Inner Join tblCourses c on l.locationId = c.locationId
                left join tblTrainings t on l.locationId = t.LocationId and c.courseId = t.courseId
                Inner Join tblTrainingDates d on c.dateid=d.dateid 
                Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId  
                Inner Join tblInstructors i on ic.instructorId = i.instructorId" 
      FilterExpression="LocationId = '{0}'" >
<FilterParameters>
 <asp:ControlParameter ControlID="ddlLocation" Name="LocationId" 
  PropertyName="SelectedValue" Type="Int32" />
 </FilterParameters>
</asp:SqlDataSource>

Isn't that a very peculiar database design? Why would you want to store the number of available seats on *each row* in the trainings table? There will only be one row with the correct value, the others will be garbage... — mortb, Jun 17 '13 at 14:16
Sounds like you need a unique constraint on (DateId, LocationId, CourseId) — 000, Jun 17 '13 at 14:16
I would think that the some table (perhaps tblCourses) would have the total number of available seats for each course. Then you would subtract the number of registered participants(they seem to be rows in the trainings table?) from that total number of available seats to get the current number of available seats. — mortb, Jun 17 '13 at 14:18
You could use TOP - but I wouldn't recommend that unless you _absolutely_ have to — penguat, Jun 17 '13 at 14:23
Also, what happens if a seat is cancelled? You don't want the smallest value then. — penguat, Jun 17 '13 at 14:25
@mortb, yes, tblCourses has Capacity_Seating. In training table it is called available_Seats. As the name suggests, that's available seats. penguat, if a seat is cancelled, it doesn't have to be the smallest, it goes back to available_Seats and updates. For instance, if availableSeat is 29 and a seat is cancelled, it will now be 30. — Kenny, Jun 17 '13 at 14:34
I up voted your query; the screenshots made fairly easy to understand your problem! — mortb, Jun 18 '13 at 08:51

score 1 · Answer 1 · answered Jun 17 '13 at 14:19

1

When a new user signs up for the course, you need to

UPDATE table_name
SET AvailableSeats = ...
WHERE TrainingID=... AND CourseId=...

rather than doing an INSERT.

There should only be one row with a unique (DateId, LocationId, CourseId) set of values.

answered Jun 17 '13 at 14:19

000

26,951
10
71
101

There is a tblDate table with unique dateId. There is tblLocation with unique locationId, same with tblCourses and tblInstructors. TblTrainings is like the main table where you would have 1:M relationship with the tables I mentioned above. – Kenny Jun 17 '13 at 14:24
@Kenny I'd recommend a composite primary key, made up of all three of them. Don't put them together yourself: SQL server can handle a multi-part key effetively on its' own. See http://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008 – penguat Jun 17 '13 at 14:26

mortb · Accepted Answer · 2013-06-17T14:43:28.593

1

You could try a subquery to count the rows and subtract from the defualt Capacity_Seating :

select DISTINCT l.LocationId,
c.courseId, 
c.coursename, 
c.Capacity_Seating - (select count(*) 
      from tblTrainings t1
      where l.locationId = t1.LocationId and c.courseId = t1.courseId) as
availableSeats,
d.dateid,
d.trainingDates, 
d.trainingtime, 
c.CourseDescription,
i.instructorName, 
l.location,
l.seating_capacity 
                    from tblLocations l
                    Inner Join tblCourses c on l.locationId = c.locationId
                    left join tblTrainings t on l.locationId = t.LocationId and c.courseId = t.courseId
                    Inner Join tblTrainingDates d on c.dateid=d.dateid 
                    Inner Join tblCourseInstructor ic on c.courseId = ic.CourseId  
                    Inner Join tblInstructors i on ic.instructorId = i.instructorId

edited Jun 17 '13 at 14:43

answered Jun 17 '13 at 14:34

mortb

9,361
3
26
44

PERFECT @mortb. That worked like a dream. I don't know what will happen if a seat is cancelled and is put back as penguat indicated. I will test that later but this query seems to work. – Kenny Jun 17 '13 at 14:36
This will work for cancelled seats if you DELETE the row from tblTrainings when a seat is cancelled. If you not remove the row I would guess you mark it somehow (like a column named "Cancelled" which gets the value 1 when a seat is cancelled). If you have the solution with a "cancelled" column you just need to put and cancelled = 0 into the where clause of the sub query – mortb Jun 17 '13 at 14:40
Yes, I just deleted a row and it put it back in there. So, it works great. Thanks a bunch. – Kenny Jun 17 '13 at 14:51

How do I show only one record - the record with smallest value?

2 Answers2