Comparing subqueries - 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)

Comparing subqueries

Author

Topic

fnsmedia
Starting Member

14 Posts

Posted - 2003-02-04 : 11:00:29

I am trying to generate a list of lapsed clients from our sql7 db of those clients that have not placed orders SINCE 01/01/02.

I have tried selecting all those companies that are not equal in two subqueries as below but my operator is generating a syntax error. Can anyone help?

SELECT dbo.Company.Cmp_Id,
dbo.Company.Cmp_Name
FROM dbo.Company
WHERE EXISTS (SELECT DISTINCT dbo.Company.Cmp_Id
FROM dbo.Company, dbo.Contact, dbo.MarketingCompany, dbo.CompanyAddress, dbo.Address, dbo.ContactMethodLink, dbo.ContactMethod, dbo.ContactMethodType, dbo.Instruction
WHERE (dbo.Instruction.INS_Cntct_id = dbo.Contact.Cntct_id AND
dbo.Company.Cmp_MC_Id = dbo.MarketingCompany.MC_id AND
dbo.Contact.Cntct_id = dbo.ContactMethodLink.CML_Cntct_Id AND
dbo.ContactMethodLink.CML_CM_Id = dbo.ContactMethod.Cm_Id AND
dbo.ContactMethod.CM_CMT_Id = dbo.ContactMethodType.CMT_Id AND
dbo.CompanyAddress.COA_Add_Id = dbo.Address.Add_Id AND
dbo.Company.Cmp_Id = dbo.CompanyAddress.COA_Cmp_Id AND
dbo.Contact.Cntct_Cmp_Id = dbo.Company.Cmp_Id) AND
dbo.ContactMethodType.CMT_Id ='2' AND
dbo.Instruction.Ins_Created < '01/01/02' )
<>
(SELECT DISTINCT dbo.Company.Cmp_Id
FROM dbo.Company, dbo.Contact, dbo.MarketingCompany, dbo.CompanyAddress, dbo.Address, dbo.ContactMethodLink, dbo.ContactMethod, dbo.ContactMethodType, dbo.Instruction
WHERE (dbo.Instruction.INS_Cntct_id = dbo.Contact.Cntct_id AND
dbo.Company.Cmp_MC_Id = dbo.MarketingCompany.MC_id AND
dbo.Contact.Cntct_id = dbo.ContactMethodLink.CML_Cntct_Id AND
dbo.ContactMethodLink.CML_CM_Id = dbo.ContactMethod.Cm_Id AND
dbo.ContactMethod.CM_CMT_Id = dbo.ContactMethodType.CMT_Id AND
dbo.CompanyAddress.COA_Add_Id = dbo.Address.Add_Id AND
dbo.Company.Cmp_Id = dbo.CompanyAddress.COA_Cmp_Id AND
dbo.Contact.Cntct_Cmp_Id = dbo.Company.Cmp_Id) AND
dbo.ContactMethodType.CMT_Id ='2' AND
dbo.Instruction.Ins_Created > '01/01/02' )

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-04 : 12:31:37

NORMALIZATION to Infinety and Beyond....

Seems like you've implemented a logical model here...

Anyway, I would start out and identify what I'm trying to get, and then build from there.

For example, you know the population you want:

SELECT Key FROM Instruction WHERE Ins_Created < '01/01/02'

I would then do an existance check against this...

Other than knowing the structure of you db, it's kind of diff. to assist.

Good Luck

Brett

8-)

fnsmedia
Starting Member

14 Posts

Posted - 2003-02-05 : 04:04:01

Thanks.

I have the logic all sorted out and know exactly what we want to pull out but am having difficulty attacking it. In order to get a list of clients that have not ordered anything since 01/01/02 would it be more efficient to find company_id where MAX order created dates (INS_CREATED) are less than 01/01/02

OR

streamline my previous coding into 2 views: dbo.vw_Lapsed_LessThan and dbo.vw_Lapsed_GREATERThan. and try to get a recordset where dbo.vw_Lapsed_LessThan IS NOT EQUAL TO dbo.vw_Lapsed_GREATERThan.

I am confused

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-05 : 04:23:50

Use the MAX version..

But first can you write this with INNER JOIN syntac instead of the ugly CROSS JOIN method.

Something like...


Select C.Cmp_Id, C.Cmp_Name 
FROM dbo.Company AS C
INNER JOIN dbo.Contact AS CN ON CN.Cntct_Cmp_Id = C.Cmp_Id
WHERE EXISTS
(
SELECT 1
FROM Instruction I
WHERE CN.Cntct_id = I.INS_Cntct_id
GROUP BY I.INS_Cntct_id 
HAVING MAX(I.Ins_Created) < '20020101' 
)

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources