Question about inner join.... - 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)

Question about inner join....

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2004-01-29 : 08:20:04

Hi Folks,
I have a query like this:

SELECT IDENTITY(int, 1,1) AS row_id,
CASE WHEN b.nbk_id IS NULL
THEN NULL
ELSE b.nbk_id
END AS nbk_id,
a.agent_logon_id,
CONVERT(varchar(11),a.datestamp,120) AS [Effective_date],
b.node_id
INTO #TempAgent
FROM tbl_a a
LEFT OUTER JOIN tbl_b b ON a.agent_logon_id = b.acd_id Id
AND CONVERT(varchar(11), a.datestamp, 120) >= CONVERT(varchar(11), b.effective_date, 120)
AND b.effective_date = (SELECT DISTINCT MAX(y.effective_date) FROM tbl_y y
WHERE ISNULL(y.nbk_id,'') = ISNULL(b.nbk_id,'')
AND y.node_id = b.node_id
AND y.effective_date <= a.datestamp)
WHERE a.datestamp BETWEEN CONVERT(varchar(11),'10/1/2003',120) AND CONVERT(varchar(11),'10/1/2003',120)
ORDER BY b.nbk_id

In the subquery I am doing a join between nbk_id on table y and nbk_id on tbl_b. I want to retain the records which have null value in the column nbk_id, hence i have used a ISNULL function in the join.
Is there a better way of doing this,
i cannot use a outer join in the sub query because there is a outer join in the outer query.
Are there any suggestions to redesign the query.

Thank you

Ramdas Narayanan
SQL Server DBA

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-01-29 : 09:37:34

Without table structures, sample data and desired output, analyzing
this query is very difficult. However, a few things do jump out at me:

In the main query SELECT clause...

, CASE WHEN b.nbk_id IS NULL THEN NULL ELSE b.nbk_id END AS nbk_id

this statement is confusing and seems unnecessary

In the join...

AND CONVERT(varchar(11), a.datestamp, 120) >= CONVERT(varchar(11), b.effective_date, 120)

converting them to character datatypes and then evaluating them seems to be extra work. I believe datetime fields evaluate correctly compared against eachother (meaning 10/3/2003 10:00AM is less than 10/3/2003 10:01AM)

In the subquery...

SELECT	DISTINCT MAX(y.effective_date)

DISTINCT and an aggregate like MAX() is rarely necessary but with those joins and such it may be.

In the main query...

WHERE	a.datestamp BETWEEN CONVERT(varchar(11),'10/1/2003',120) 
AND	CONVERT(varchar(11),'10/1/2003',120)

Would this bring back anything? It seems there is no time gap to evaluate. Does the query bring back any results?

However, with sample data and table build statements, a better analysis could be made

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources