?
Solved

Sql exclude records that are in table

Posted on 2006-05-18
5
Medium Priority
?
7,529 Views
Last Modified: 2010-10-05
I have two tables "Contracts"   and "BinNumbers" .  Table  "BinNumbers" contains one field "BinNumbers"   There are 20 records with numbers 1-20.   Table "Contracts"  contains 2 fields "Status" and "BinNumber".  Contracts.BinNumber is assigned a number that is in BinNumbers.BinNumbers.  Contracts.Status can have values of "Current", "Complete", or "Incomplete".  A user can select a BinNumber from a drop down and assign it to a contract.  If a Contract has a status of "Complete", or "Incomplete then the BinNumber is in use and I don't want it to be available for selection. I need a sql statement that will :
Select all BinNumbers.BinNumbers except those in Contract.BinNumbers WHERE the Contract.Status = "Complete" or the Contract.Status="Incomplete.
0
Comment
Question by:Moed
  • 2
  • 2
5 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 16707256
Hi Moed,

Select BinNumbers.BinNumbers Where BinNumbers.BinNumbers Not In (Select Contract.BinNumbers From Contract Where Status <> 'Complete' And Status <> 'Incomplete')

Should do it.

Tim Cottee
0
 
LVL 2

Author Comment

by:Moed
ID: 16707263
Sorry I made a mistake.  If the contract has a status of "Complete" then the number is available for use.  If the status is anything else then the number is not available for use. So the sql would be

Select all BinNumbers.BinNumbers except those in Contract.BinNumbers WHERE the Contract.Status <>"Complete"

This is the way they are currently selected.   I need to combine these two statements into one.

This is qNonCompleteContracts

SELECT ContractInformation.*, ContractInformation.txtInstallStatus
FROM ContractInformation
WHERE (((ContractInformation.txtInstallStatus)<>"Complete" And (ContractInformation.txtInstallStatus)<>"Incomplete"));


SELECT [BinNumber].[txtBinNumber]
FROM BinNumber LEFT JOIN qNonCompleteContracts ON [BinNumber].[txtBinNumber] = [qNonCompleteContracts].[txtBinNumber]
WHERE ([qNonCompleteContracts].[txtBinNumber] Is Null);
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16707364
SELECT BinNumbers
FROM BinNumbers B
WHERE NOT EXISTS ( SELECT 1 FROM Contract c WHERE C.BinNumber = B.BinNumber AND C.Status NOT IN ('Complete', 'InComplete'))
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 750 total points
ID: 16707371
Moed,

Even simpler then
Select BinNumbers.BinNumbers Where BinNumbers.BinNumbers Not In (Select Contract.BinNumbers From Contract Where Status <> 'Complete')

Tim
0
 
LVL 2

Author Comment

by:Moed
ID: 16707869
Thanks to both.
TimCottee was first in so you get the points.  You missed the FROM  in the first select statement so I'll give a B grade.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question