Help:SQL to exclude items in other table

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

General SQL Server Forums

New to SQL Server Programming

Help:SQL to exclude items in other table

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-08-29 : 18:56:34

I can find my way around MS SQL but when it comes to more complex queries, I don't know if I have the head for it. If someone would take a look and either help me come up with the query, or point me in the right direction, I'd appreciate it.

I'm working on a scheduling website which users (uid) request a slot(sid) out of available slots. This availability is determined by group(gid), AM/PM shift(shid) and date (sdate).

The tables/data:

SLOTS (Data):
sid|sdate |gid|shid
256|'9/1/08'|55 |11
261|'9/1/08'|55 |11
262|'9/1/08'|55 |11

REQUESTS (Data):
rqid|uid|sid
3 |1 |256

I'm looking to retrieve a list of "available" slot ID's - I.e. if BOB is in the group Sales (55) and works nights shid 11, what slots are available that match that group/shift on 9/1/2008 but that nobody else has selected.

I have this so far:

SELECT DISTINCT slots.sid
FROM svac_requests
INNER JOIN slots ON slots.sid <> requests.sid
WHERE (slots.gid = 55)
AND (slots.shid = 11)
AND (slots.sdate = '9/1/08')

This lists all the slots matching gid/shid/date.

I would like the query to list 261 and 262 only as slot 256 was selected by request 3

Thanks in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-29 : 20:20:24

[code]
SELECT DISTINCT slots.sid
FROM svac_requests
~~INNER~~ LEFT JOIN slots ON slots.sid <> = requests.sid
WHERE (slots.gid = 55)
AND (slots.shid = 11)
AND (slots.sdate = '9/1/08')
AND (requests.sid is null)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:16:19

it should be other way round:-

SELECT DISTINCT slots.sid
FROM   slots s
LEFT JOIN  svac_requests  r
ON s.sid   = r.sid
WHERE  (s.gid    = 55)
AND    (s.shid   = 11)
AND    (s.sdate  = '9/1/08')
AND    (r.sid is null)

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2008-09-03 : 17:16:41

Thanks! I believe enterprise manager changed LEFT JOIN to LEFT OUTER JOIN, but it's working nicely.

SELECT DISTINCT slots.sid
FROM slots
LEFT OUTER JOIN requests
ON slots.sid = requests.sid
WHERE (slots.gid = 55)
AND (slots.shid = 11)
AND (slots.sdate = '9/24/2008')
AND (requests.sid IS NULL)

Thanks again. This forum has been a life-saver...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:05:25

quote:
Originally posted by Jaypoc

Thanks! I believe enterprise manager changed LEFT JOIN to LEFT OUTER JOIN, but it's working nicely.

SELECT DISTINCT slots.sid
FROM slots
LEFT OUTER JOIN requests
ON slots.sid = requests.sid
WHERE (slots.gid = 55)
AND (slots.shid = 11)
AND (slots.sdate = '9/24/2008')
AND (requests.sid IS NULL)

Thanks again. This forum has been a life-saver...

they are one and the same

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources