Difficult SQL query - 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

SQL Server Development (2000)

Difficult SQL query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-18 : 08:37:23

Graeme writes "I am using 2 views:
customersAndOrders2005 and customersAndOrders2006
The views contain the same fields.

I want to display only those details which are in the 2005 view
but not in 2006 view.

I could use:
SELECT * FROM a
MINUS
SELECT * FROM b

except that I don't want it to use all of the columns
to work this out. I want to use surname and postcode.

So,
SELECT surname, postcode FROM a
MINUS
SELECT surname, postcode FROM b

This returns the rows that I want, but I want
all of the field '*' in my result set.

How do I do this? I am only using SQL 2000, SP4.
(I realise MINUS is not valid in sql 2000)

I could use row_number, but sql 2000 does have this either.

Thanks!"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 08:44:17

[code]Select a.*
from a
left join b on a.surname = b.surname and a.postcode = b.postcode
where b.surname is NULL and b.postcode is NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 09:25:22

Too bad it is not SQL Server 2005.

SELECT surname, postcode from a
except
select surname, postcode from b

Peter Larsson
Helsingborg, Sweden

gbarnett
Starting Member

1 Post

Posted - 2007-01-19 : 03:26:33

Thanks for your replies.

I resolved the problem by creating a new field surcode (surname concateated postcode. I then said something like:
select * from 2005
where surcode not in ( select surcode from 2006).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 03:36:40

Why?
The suggestion Harsh provided does the same thing and faster.

This is fast too

Select	a.*
from	a
where	not exists (select null from b where b.surname = a.surname and b.postcode = a.postcode)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-19 : 03:46:44

Some people doesn't bother to accept the suggestions...they are happy to implement their own...even if it causes them lot more efforts and bad performance.

After they get performance hit, they turn again here to ask for tuning their slow performing queries.(Reactive rather than Proactive approach).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources