LEFT JOIN RESULTS - 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 2005 Forums

Transact-SQL (2005)

LEFT JOIN RESULTS

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-17 : 06:11:45

Using a great left join connection where ~500,000 rows are either NULL or something important on one side or the other

NULL good
good NULL
NULL good

all the way down on 6 columns, keeping the tables apart. But I need to get rid of all the BOTH NULL rows. What's the syntax for that?

using:

where prcode is not null and stkcode is not null

I get no rows. Shoulda tried OR

Need to quickly give the boss financial data for comparison.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-17 : 06:17:18

shows us your query

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 06:40:12

AND NOT (Col1 IS NULL AND Col2 IS NULL)

E 12°55'05.63"
N 56°04'39.26"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-06-17 : 07:23:49

This did it

select
distinct
prcode,
stkcode,
det_quantity,
det_unit_price,
pr_price,
stk_baseprice,
det_nett,
det_curr_nett,
det_gross,
det_VAT,
st_nett,
det_date_putin
from sl_pl_nl_detail det
left join prc_price_recs p on p.prcode = det.det_price_code
left join stk_stock s on s.stkcode = det.det_stock_code
inner join sl_transactions st on det.det_header_ref = st.st_header_ref
where prcode is not null or stkcode is not null
order by det_date_putin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 07:45:55

1. Run your query three times.
2. Write down the time for fastest execution.
3. Replace your WHERE clause with the one suggested 06/17/2009 : 06:40:12
4. Run edited query three times
5. Write down the time for fastest execution.
6. Post the results here.

I have a hunch that my suggestion with AND will be a little faster than the suggestion with OR.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources