Help with unmatched query in SQL Server 2005

For years I've been using unmatched queries in Access (I have to admit to NEVER writing an unmatched query by hand - always used the Wizard) but now that I've moved my data to SQL Server I'm having a bit of a problem figuring out how to achieve the same results using stored procedures.

In one stored procedure, I return a set of all customers with the count of open orders:

SELECT     fldCustID, COUNT(fldOrderID) AS OpenOrders
FROM         dbo.tblOrders
WHERE     (fldCompleted = 0)
GROUP BY fldCustID

That part's easy.  Now I need to compare this to my customers table:

SELECT     fldCustID
FROM         dbo.tblCustomers
WHERE     (fldInactive = 0)

 and find all customers in the second resultset that don't exist in the results of the first stored procedure.

I'm sure this is easy but it's got me stumped.  Thanks in advance for your help!
LVL 1
penlandtAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rw3adminConnect With a Mentor Commented:
This should work on your query,
SELECT     fldCustID
FROM       dbo.tblCustomers
Left Join
(      SELECT     fldCustID, COUNT(fldOrderID) AS OpenOrders
      FROM         dbo.tblOrders
      WHERE     (fldCompleted = 0)
      GROUP BY fldCustID ) as A
ON      dbo.tblCustomers.fldCustID=A.fldCustID
WHERE     (fldInactive = 0) and A.fldCustID is Null


but I think basically this is what you want ... get fldCustID which is not in tblOrders table

SELECT     dbo.tblCustomers.fldCustID
FROM       dbo.tblCustomers
Left Join
      dbo.tblOrders
ON      dbo.tblOrders.fldCustID =dbo.tblCustomers.fldCustID
WHERE     (dbo.tblCustomers.fldInactive = 0) and (dbo.tblOrders.fldCompleted = 0) and
                dbo.tblOrders.fldCustID is Null
0
 
dduserCommented:
Select fldCustId from dbo.tblCustomers Inner Join (SELECT     fldCustID, COUNT(fldOrderID) AS OpenOrders
FROM         dbo.tblOrders
WHERE     (fldCompleted = 0)
GROUP BY fldCustID) as DerivedTable on DerivedTable.fldCustId <> tblCustomers.fldCustId where fldInactive = 0

Regards,

dduser
0
 
penlandtAuthor Commented:
Thanks for the quick reply.  This gave me a cartesian product (I stopped at 200,000+ results from a customers table of +/- 1600 rows).  Any idea where the problem is?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
dduserCommented:
Try this

Select fldCustId from dbo.tblCustomers where fldInactive = 0 and fldCompled = 0

Check this out this should also work...

Regards,

dduser
0
 
penlandtAuthor Commented:
Nope - there is no "fldCompleted" in the customers table - it's in the orders table.
0
 
dduserCommented:
Select fldCustId from dbo.tblCustomers where fldInactive = 0 and fldCustId not in (SELECT     fldCustID FROM      dbo.tblOrders
WHERE     (fldCompleted = 0)
GROUP BY fldCustID)

Regards,

dduser
0
 
penlandtAuthor Commented:
Thank You.  The first of the two was what I was looking for.  The second one won't work for me because all of the customers have at least one record in the orders table.  Thanks again for the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.