Moe DeShong
asked on
Sql exclude records that are in table
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="Incomplet e.
Select all BinNumbers.BinNumbers except those in Contract.BinNumbers WHERE the Contract.Status = "Complete" or the Contract.Status="Incomplet
ASKER
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.txtIns tallStatus
FROM ContractInformation
WHERE (((ContractInformation.txt InstallSta tus)<>"Com plete" And (ContractInformation.txtIn stallStatu s)<>"Incom plete"));
SELECT [BinNumber].[txtBinNumber]
FROM BinNumber LEFT JOIN qNonCompleteContracts ON [BinNumber].[txtBinNumber] = [qNonCompleteContracts].[t xtBinNumbe r]
WHERE ([qNonCompleteContracts].[ txtBinNumb er] Is Null);
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.txtIns
FROM ContractInformation
WHERE (((ContractInformation.txt
SELECT [BinNumber].[txtBinNumber]
FROM BinNumber LEFT JOIN qNonCompleteContracts ON [BinNumber].[txtBinNumber]
WHERE ([qNonCompleteContracts].[
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'))
FROM BinNumbers B
WHERE NOT EXISTS ( SELECT 1 FROM Contract c WHERE C.BinNumber = B.BinNumber AND C.Status NOT IN ('Complete', 'InComplete'))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
Select BinNumbers.BinNumbers Where BinNumbers.BinNumbers Not In (Select Contract.BinNumbers From Contract Where Status <> 'Complete' And Status <> 'Incomplete')
Should do it.
Tim Cottee