Select from select (missing row)

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

General SQL Server Forums

New to SQL Server Programming

Select from select (missing row)

Author

Topic

Soderpat
Starting Member

1 Post

Posted - 2010-10-28 : 07:31:38

Hi,

The thing is to left join two sub-questions as

SELECT * FROM

(Select a, b, c from INNER JOIN c,d on and INNER JOIN where group by... and so on... )sec

Then,

LEFT JOIN SELECT e,f on and INNER JOIN where group by... and so on... ) sec_t_1

ON(sec.holkey_ref = sec_t_1.holkey_ref and sec.sec_ref = sec_t_1.sec_ref)

WHERE

sec_t_1.perf_to_date = TO_DATE ('&t0', 'yyyy-mm-dd') and

sec.perf_to_date = TO_DATE ('&t1', 'yyyy-mm-dd')

I know that the result should contain 37 rows but when executing this code the result contais 36 rows. However, the right result appears when I use perf_to_date = TO_DATE ('&t0', 'yyyy-mm-dd') as a condition in the subquestion sec_t_1 instead of having it in the main question as above.

Shouldn't the two ways of doing it give exactly the same result?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-01 : 12:23:54

nope they're different. see

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-02 : 03:57:09

Also this is SQL Server forum. Post your oracle questions at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources