joins - 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

Transact-SQL (2000)

joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-10 : 08:14:57

Alex writes "Hi Sql Team
I need to pull records from one table lets call it MyList (ID_list,DATE_ACCESSED)
Where other tables SUB_NOTES(ID,ID_LIST,DATE_ACCESSED1)
SUB_PENDING(ID,ID_LIST,DATE_ACCESSED2)
SUB_TEXT(ID,ID_LIST,DATE_ACCESSED3)
SHOULD HAVE DATE_ACCESSED1,DATE_ACCESSED2,DATE_ACCESSED3 and DATE_ACCESSED lesser than input date
ID_LIST is primary key for MyList and foreign key for sub tables. Sub tables will
Not necessarily have records for every record from MyList. I have been running
this report from asp using shape command but I want to move it into stored procedure:
I've been using outer join like this but it gives me slightly wrong result:

SELECT distinct L.ID_LIST
FROM MYLIST L
left outer join SUB_NOTES SN on L.ID_LIST = SN.ID_LIST
left outer join SUB_PENDING SP on L.ID_LIST = SP.ID_LIST
left outer join SUB_TEXT ST on L.ID_LIST = ST.ID_LIST
WHERE
DATEDIFF(d,isnull(SN.DATE_ACCESSED1, '1/1/1900'),@eDate) > 0
and DATEDIFF(d,isnull(SP.DATE_ACCESSED2, '1/1/1900'),@eDate) > 0
and DATEDIFF(d,isnull(ST.DATE_ACCESSED3, '1/1/1900'),@eDate) > 0
and DATEDIFF(d,isnull(L.DATE_ACCESSED, '1/1/1900'),@eDate) > 0
order by L.ID_LIST

Also I've been trying subquery but it takes ridiculously long
I do not in favour of outer join it can be any method as long does the job
OS: WIN2000 SERVER
sql 2000 SP3

Thanks"

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-06-11 : 11:43:00

quote:
Also I've been trying subquery but it takes ridiculously long

Running the query like below may improve the performance because you will not need to hash as many records together when the tables are joined. I really dont have anything to say about the query producing "slightly wrong results" though. You'd need to be much more specific to help me understand.


SELECT distinct L.ID_LIST
FROM (select * from MYLIST where DATEDIFF(d,isnull(L.DATE_ACCESSED, '1/1/1900'),@eDate) > 0) L
left outer join 
	(select * from SUB_NOTES where DATEDIFF(d,isnull(SN.DATE_ACCESSED1, '1/1/1900'),@eDate) > 0) SN on L.ID_LIST = SN.ID_LIST
left outer join 
	(select * from SUB_PENDING where DATEDIFF(d,isnull(SP.DATE_ACCESSED2, '1/1/1900'),@eDate) > 0) SP on L.ID_LIST = SP.ID_LIST
left outer join 
	(select * from SUB_TEXT where DATEDIFF(d,isnull(ST.DATE_ACCESSED3, '1/1/1900'),@eDate) > 0) ST on L.ID_LIST = ST.ID_LIST 
order by L.ID_LIST

Hope that helps...

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources