Select statement not working - 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)

Select statement not working

Author

Topic

rett
Starting Member

35 Posts

Posted - 2004-10-02 : 23:18:07

I have a select statement that's selecting orders that exist in '2003,2002,2001" but not in 2001. I am trying to use the same table to select from. This table can have over 500,000 thousand records or more. I am not sure how to tackle this statement without taking so long to select.

Here's my statement:

SELECT DISTINCT NAME_ID, A.ISSUE_ID
FROM
(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID IN('20030331','20020331','20020331')) AS A,

(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID = '20020331') AS B,

dbo.ORDERS AS C WHERE C.ISSUE_ID <> '20010331' AND A.ISSUE_ID <> C.ISSUE_ID

Thanks

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 00:36:07

if all you need are the orders from 2002 and 2003 why not just do


select name_id, issue_id from dbo.orders 
where issue_id in ('20030331', '20020331')

But from looking at your query you arent joining B to anything... so thats going to return every possible combination of B to your result set . Plus you are selecting from subqueries... which I know isnt necessary. Can you post some more information? db schema and what you are trying to do. Theres a better way to write this.

rett
Starting Member

35 Posts

Posted - 2004-10-03 : 13:03:48

I am sorry the statement should look like this

SELECT DISTINCT NAME_ID, A.ISSUE_ID
FROM

**** The first select I want to now if a individual has done business with a comany in "2003033","20020331","20010331' than I need to know if they did not do business with the company in '20000331". I am trying to compare the 2 selects.

(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID IN('20030331','20020331','20020331')) AS A,
(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID = '20010331') AS B,
dbo.ORDERS AS C WHERE C.ISSUE_ID <> '20010331' AND A.ISSUE_ID <> B.ISSUE_ID AND A.NAME_ID <> B.NAME_ID

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 14:39:10

quote:
Originally posted by rett

I am sorry the statement should look like this

SELECT DISTINCT NAME_ID, A.ISSUE_ID
FROM

(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID IN('20030331','20020331','20020331')) AS A,
(SELECT ISSUE_ID FROM dbo.ORDERS WHERE ISSUE_ID = '20010331') AS B,
dbo.ORDERS AS C WHERE C.ISSUE_ID <> '20010331' AND A.ISSUE_ID <> B.ISSUE_ID AND A.NAME_ID <> B.NAME_ID

That still really wont get you the results you want I think..

quote:

**** The first select I want to now if a individual has done business with a comany in "2003033","20020331","20010331' than I need to know if they did not do business with the company in '20000331". I am trying to compare the 2 selects.

Maybe you should try a different approach. Something like this?


select issue_id, name_id from dbo.orders where issue_id 
IN('20030331','20020331','20010331') 
AND name_id not in (select name_id from dbo.orders where issue_id='20000331')

Is this what you were trying to do?


SELECT distinct A.name_id,A.issue_id
FROM
(SELECT ISSUE_ID,NAME_ID FROM dbo.ORDERS WHERE ISSUE_ID IN('20030331','20020331','20010331')) AS A, 
(SELECT ISSUE_ID,NAME_ID FROM dbo.ORDERS WHERE ISSUE_ID = '20000331') AS B
WHERE A.name_id <> b.name_id

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 14:44:57

Forget about that... I think i just realized what you are trying to do. Could you post a little more information about your orders table. Is name_id the company id or the customer id?

rett
Starting Member

35 Posts

Posted - 2004-10-03 : 15:20:51

Thanks for your help slacker. Here's an example

I have to customers or company

Company Id Order Date
41001 20040331
41001 20030331
41001 20020331
41001 No order in 20010331

45500 20040331
45500 20030331
45500 20020331
45500 20010331

My select should only return the company id 41001

I hope this is helpful

Thanks

41002

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 15:35:23

Oh then my original assumption was right... just try this...


select issue_id, name_id from dbo.orders where issue_id 
IN('20040331','20030331','20020331') 
AND name_id not in (select name_id from dbo.orders where issue_id='20010331')

rett
Starting Member

35 Posts

Posted - 2004-10-03 : 16:00:12

Slacker,

We are almost there. The problem that I am having now is that the select is picking up company's that are in either order date '20040331' or '20030331' or '20020331'. I need for the select to check and make sure that the company exists in all 3 order dates and not in '20010331'

Thanks

rett
Starting Member

35 Posts

Posted - 2004-10-03 : 16:28:20

slacker
Posting Yak Master

115 Posts

Posted - 2004-10-03 : 16:51:56

quote:
Originally posted by rett

Slacker,

We are almost there. The problem that I am having now is that the select is picking up company's that are in either order date '20040331' or '20030331' or '20020331'. I need for the select to check and make sure that the company exists in all 3 order dates and not in '20010331'

Thanks

gotcha.

Try this... There may be a better way.. but this is what I came up with


select issue_id, name_id from dbo.orders a
WHERE
(
select count( distinct issue_id ) 
from orders b where a.name_id=b.name_id 
AND ISSUE_ID IN('20040331','20030331','20020331')
) = 3 -- 3 is the number of dates
AND 
a.name_id NOT IN (select name_id from orders where issue_id='20010331')

rett
Starting Member

35 Posts

Posted - 2004-10-03 : 17:04:13

THANKS VERY, VERY MUST SLACKER

That works !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources