Select from 1st query with 2nd query as filter

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 from 1st query with 2nd query as filter

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-31 : 14:35:53

I'm not sure how to do this but might be something simple for you SQL gurus. I have one query in which I pull everything. Then I have a second query that selects the record with a date filter. What I wanted to do is pull everything that is no longer within that date range or base on my situation, inactive customers.

Example:

This pulls all records of existing customers.

[CODE]
SELECT ID, VENDOR_NAME, CUSTOMER_PHONE
FROM CUSTOMER
[/CODE]

This pulls records of customers that still makes purchase within that date range.


SELECT DISTINCT CO.CUST_ID
FROM CUSTOMER_ORDER CO
WHERE CO.ORDER_DATE BETWEEN @STARTDATE AND @ENDDATE

What I want to do is that any customers that does not appear in the second query to have it display the record, but any that is within that date range still means the customer is active and not display it. At the current situation, I can only diplay customers that are active, or still listed within the second query and that's not what I want.

Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-31 : 14:37:45

SELECT ID, VENDOR_NAME, CUSTOMER_PHONE
FROM CUSTOMER
WHERE CUST_ID NOT IN (
SELECT DISTINCT CO.CUST_ID
FROM CUSTOMER_ORDER CO
WHERE CO.ORDER_DATE BETWEEN @STARTDATE AND @ENDDATE)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-05-31 : 14:52:14

Sweet!!! Didn't know it was that easy. I tried that but didn't know I can use "NOT IN". Been using "IN", but now I know.

A million thanks!!!

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-01 : 00:18:50

Even this will work ,

SELECT DISTINCT CO.CUST_ID
FROM CUSTOMER_ORDER CO
WHERE CO.ORDER_DATE NOT BETWEEN @STARTDATE AND @ENDDATE

Thanks,
Vivek

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-01 : 08:58:26

quote:
Originally posted by vivek.kumargupta

Even this will work ,

SELECT DISTINCT CO.CUST_ID
FROM CUSTOMER_ORDER CO
WHERE CO.ORDER_DATE NOT BETWEEN @STARTDATE AND @ENDDATE

Thanks,
Vivek

That's not really the same, now is it.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources