combine 2 queries - 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)

combine 2 queries

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2004-11-29 : 11:36:49

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 cdate between '20041001' and '20041031'

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-29 : 12:42:47

NOT TESTED


SELECT * FROM (
    select sid 
      from attlogstuds studs
inner join attendancelog attlog
	on studs.headerid=attlog.headerid
     where reportmonth=10) AS XXX
RIGHT JOIN (
   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 c6.sid is null) AS YYY
ON xxx.sid = yyy.sid

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources