SQL Select 1st return for each item only

[Denaes]

I need to search by name and return all of the records by that person. If there are duplicate fields, say bookingID, I only want to keep the first one I come across and disregard the others.

 

I know this is ghetto, but my clients changed what information they wanted stored at the last minute (I was like 98% done) and they don't realize why this is an issue. I should just make the change and move on.

 

To make the change I need to split a table into two related tables (thus the repeated data) and reflect those changes in 30 stored procedures and in the logic by which my program pulls the information out of the database... which would account for about a weeks worth of work.

 

This change is really only needed for reports, so if I could make a new SQL select statement that ignored duplicates in a given field, I'd be set.

[Cassio]

Could you give more details about the table structure and the data you store in it?

[Denaes]

Could you give more details about the table structure and the data you store in it?

 

Four relevant tables here.

 

Customer

Instructor

Schedule

Area

 

The origional job was that there where X number of locations in a Gym. The client wanted to be able to select an area and a time frame from a visual schedule (ex: You find Weight Room 1 and drag from 8am to 10am). Then you get a screen asking for you to select a customer and instructor for your database and features like repeating the schedule on multiple days.

 

This was easy. 1 table of Customers. 1 table of Instructors. 1 table w/Areas (names of areas and their codes) . 1 table which has a relation to the Area table (which area was chosen), Customer Table (The customer), Instructor Table (the instructor) and then misc info like start-end time, date, etc.

 

Now they want to be able to select two or more areas; instead of selecting a single column, they want to drag across... in case someone wants to reserve in more than one area at once (in the case of a football field dived into halves or quarters).

 

They tell me this after months of working on the logic and database and planning. It would take me a week or more to basically start over and redefine the database table structure and every bit of logic relating to the information I need to pull out... basically for the sake of a few queries.

 

Now what happens is that if someone books in 3 areas, there are 3 records in the same bookingID number and while there are some differences in the record, there is some duplication.

 

in this current instance, I need to pull every related record (I call it a Full Schedule Booking;), grabbing the customer name, instructor name, area name, start time, end time, cost and amount paid.

 

Normally it doesn't matter when information overlaps. The customer name will be the same in all the records of the same booking, as will be the start time and end time.

 

For the sake of accounting, I need to pull up various reports which involve adding the price columns together and paid columns together to get totals.

 

The problem is that if someone books in 4 areas (say the entire football field; Quarter1, Quarter 2, Quarter 3 and Quarter 4), the price will be in each of the 4 records. On a query selecting all of the bookings for the day, this will show up as 4 bookings and total the price x4.

 

I'm looking for a way to specify a datacolumn which only allows one result, the first one, then ignores the rest.

 

Ex: This is the booking ID's that would normally be brought up on a simple query: 1, 1, 1, 2, 3, 4, 5, 5, 5, 6, 6, 7. I just want to bring up 1, 2, 3, 4, 5, 6 and 7. I don't want to get the other records. Only the first with a particular field value in a specified column.

 

I know I could do this in VB just fine. My reports are supposed to be in SQL though.

 

I really just want to get these reports finished and end this program.

[Aspnot]

Try doing something like:

 

SELECT Sum(Cost) from BookingsTable GROUP BY BookingID

 

If you are just looking for the BookingIDs and don't care to do any aggregate functions, then you can just use something like:

 

SELECT DISTINCT BookingID from BookingsTable

[Cassio]

Supose you have a primary key called ScheduleID.

 

SELECT SUM(Cost) 
FROM BookingsTable B 
GROUP BY ScheduleID, BookingID 
HAVING  SheduleID = (SELECT MIN(SheduleID) FROM BookingsTable B2
WHERE B.BookingID = B2.BookingID)

 

I dont know the structure of your tables so of course this query is wrong, but it might give you an idea of what you can try.

You could as well use a store procedure with a cursor and a temporary table. You can do almost anything with it. ;)

[Denaes]

Try doing something like:

 

SELECT Sum(Cost) from BookingsTable GROUP BY BookingID

 

If you are just looking for the BookingIDs and don't care to do any aggregate functions, then you can just use something like:

 

SELECT DISTINCT BookingID from BookingsTable

 

Perfect.

 

I just added DISTINCT BookingID to my SELECT statement and it pulls out the first of multiple bookingIDs!