Need help for 3 table query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help for 3 table query

Author

Topic

ballonier
Starting Member

3 Posts

Posted - 2002-10-29 : 04:55:50

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

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-29 : 05:23:03

select trainer_id, slot_id
from tblslots s join temp1 t
left outer join tbl_bookings b
on b.trainer_id = t.trainer_id
and b.slot_id = s.slot_id
where b.trainer_id is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-29 : 05:31:16

Hmm. I'm sure there's something wrong with the relationships here that makes this harder than it should be. Anyway try something like

select trainer_id, slot_id from (
select * from temp1
cross join
tbl_slots ) estuff
left outer join tbl_trainer_bookings ttb on
ttb.trainer_id = estuff.trainer_id and
ttb.slot_id = estuff.slot_id
where
ttb.trainer_id is null
or
ttb.slot_id is null

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-29 : 05:35:25

Or use NR's solution. His looks much easier.

ballonier
Starting Member

3 Posts

Posted - 2002-10-29 : 05:49:20

All,

Thank you for the quick replies!

NR, I understand most of your solution, but
it keeps on giving me an error...
(Incorrect syntax near the keyword 'where'),
and I can't figure out what's going on.

Could you please have a look @ it again?

Thanks a lot!

Regards,

Ballonier

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-29 : 06:28:39

Sorry - forgot join needs a condition - also need to name the tables on output

select t.trainer_id, s.slot_id
from tblslots s cross join temp1 t
left outer join tbl_bookings b
on b.trainer_id = t.trainer_id
and b.slot_id = s.slot_id
where b.trainer_id is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ballonier
Starting Member

3 Posts

Posted - 2002-10-29 : 06:40:15

NR,

Thanks a lot, works like a dream now!

Hope to be able to help you one day...

Regards,

Ballonier

Edited by - ballonier on 10/29/2002 06:40:38

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources