November 25, 2010 at 7:30 am
I'm working on SQL Server 2000. Consider next query:
CREATE TABLE #T (
matricula char(7),
tipo_nota char(1),
hora datetime
)
INSERT #T VALUES ( '110985', 'E', '20090309 07:00:00' )
INSERT #T VALUES ( '110985', 'S', '20090309 15:00:00' )
-- QUERY A - Two rows (as hoped) ----
SELECT *
FROM #T AS E1
LEFT JOIN #T AS E2
ON E1.matricula = E2.matricula
WHERE E1.tipo_nota = 'E'
ORDER BY E1.matricula, E1.hora
-- QUERY B - Three rows (and one with tipo_nota = 'S' ?!)----
SELECT *
FROM #T AS E1
LEFT JOIN #T AS E2
ON E1.matricula = E2.matricula AND E1.tipo_nota = 'E'
ORDER BY E1.matricula, E1.hora
DROP TABLE #T
Why they don't give the same answer?
TIA,
Diego Buendia
BCN, Spain
November 25, 2010 at 7:44 am
The second select have a LEFT JOIN as well. So even though there is no match you still get the row. Hence the 'S' row.
/T
November 25, 2010 at 10:04 am
As Tommy said, it looks to be the left join causing you the problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2010 at 11:15 am
Thank you guys. I was obfuscated -- too much time at work --- Now it's clear crystal, but this morning it wasn't. Thanks again,
November 25, 2010 at 11:33 am
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply