combine 2 queries

[avital]

Hi,

I have 2 queries that I need to join. I have the following tables:
attendancelog :
headerid
reportmonth

attlogstuds:
headerid
sid

class:
sid
class
status
yearcode
logdate
cdate
mid
The result must return all the classes that appear in query2 but not
in query1.
I am not sure how to join the 2 queries.

Help will be appreciated :-)

Thanks

QUERY1

select sid from
attlogstuds studs
inner join
attendancelog attlog
on studs.headerid=attlog.headerid
where reportmonth=10

query2
-- students learning excl. studs left before 1th oct.

select class.SID from class

left outer JOIN ( select * from class where yearcode=26 and status=6

and ( logdate <'20041001' or CDate
< '20041001' )

) c6 ON c6.sid = class.sid and c6.mid=class.mid
and c6.cdate >= class.cdate

where class.yearcode=26 and class.status in (3,5) and
class.cdate<'20041101' and c6.sid is null

[Erland Sommarskog]

[posted and mailed, please reply in news]

avital (av*********@walla.co.il) writes:

The result must return all the classes that appear in query2 but not
in query1.
I am not sure how to join the 2 queries.

Sounds like the EXCEPT operator would have come in hand, but that
operator is not in SQL Server.

It's always a good idea to include the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it possible to post a tested solution.

The below is an untested solution. I may have missed something but
it seems to me that a simple NOT EXISTS clause is what you need.

select class.SID
from class
left JOIN (select *
from class
where yearcode=26
and status=6
and ( logdate <'20041001' or
CDate < '20041001' )
) c6
ON c6.sid = class.sid
and c6.mid = class.mid
and c6.cdate >= class.cdate
where class.yearcode=26
and class.status in (3,5)
and class.cdate<'20041101'
and and c6.sid is null
and NOT EXISTS (select *
from attlogstuds studs
join attendancelog attlog
on studs.headerid=attlog.headerid
where studs.sid = class.sid)
--
Erland Sommarskog, SQL Swhere reportmonth=10erver MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp