Need help with 3 table query

[Ballonier]

Hi all,

I need some help with a query:
I have 3 tables:

tbl_trainer_bookings
.trainer_booking_id PK
.slot_id
.trainer_id

tbl_slots
.slot_id PK

temp1 (temporary table, trainer_id could be considered PK)
.trainer_id

I need a list of all combinations of
trainer_id and slot_id that are not in
tbl_trainer_bookings.

Hope this is a clear enough explanation,
and that somebody will be able to help me!

Regards,

Ballonier

 

[DanJR]

hi there,

Firstly, I assume temp1 table contains your list of trainerID's?

I see the query as having two parts. Firstly, you need a list of slot-trainer combinations and secondly you want to limit this list where 'slot-trainer' combinations do not exist in your tbl_trainer_bookings table.

You can create a list of slot-trainer combinations by creating a cross product query. e.g.

---
SELECT temp1.trainer_id, tbl_slots.Slot_id
FROM temp1, tbl_slots
---

You now need to limit this list, excluding combinations that don't exist in you tbl_trainer_bookings table. You can do this by using a correlated subquery....Therefore, the final query looks something like this:

---
SELECT tbl_slot.slot_id, temp1.trainer_id
FROM tbl_slot, temp1
WHERE ((((SELECT count(*) FROM tbl_trainer_bookings WHERE tbl_slot.slot_id =tbl_trainer_bookings.slot_id and temp1.trainer_id = tbl_trainer_bookings.trainer_id))=0))
---

I have used the correlated subquery to count the number of records in tbl_trainer_bookings table for each of the trainer-slot combinations. Therefore, if the subquery returns a count of zero, this means no trainer-slot combination exists. Thus, the final query only displays records where the subquery returns 0.

I hope this helps. Please let me know if you require further help,

Cheers,
Dan

 

[Ballonier]

Dan, thanks a lot to take time out for the reply!

In the meantime, I found out a way.

This one works probably only on SQL server
though:

SELECT temp1.trainer_id,
tbl_slots.slot_id
FROM tbl_slots
CROSS JOIN temp1
LEFT OUTER JOIN tbl_trainer_bookings
ON tbl_trainer_bookings.trainer_id = temp1.trainer_id
AND tbl_trainer_bookings.slot_id = tbl_slots.slot_id
WHERE tbl_trainer_bookings.trainer_id IS NULL

I tried to do it with a correlated subquery, and
didn't figure that out, until somebody helped me with
this one.

Thanks anyway!

Regards,

Ballonier

 

[DanJR]

Hi Ballonier,

Thanks heaps for posting back how you got your desired result. I think your right, I don't think MS Access (Jet) supports the CROSS JOIN syntax but does support the concept (ie cross-product)

In Access 2002, the user now has the options of enabling ANSI SQL-92 query mode, provided their database is in Access 2002 format (in 2002, the you can leave the database 'format' in 2000).

Laul Litwin, Ken Getz, and Mike Gunderloy have a table in their latest Access book that lists the major differences between Access SQL, SQL-92, Jet SQL-92, and T-SQL.

Thanks again, as I now have an example of MS Access verses SQL Server using cross joins.

Cheers,
Dan