SQL And/Or/<> to exclude records

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

Other Forums

MS Access

SQL And/Or/<> to exclude records

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-23 : 17:20:42

Hello,

How are you today?

I wish to exclude records from my resultset if the contents of fields I specify match specified criteria.


SELECT Final_Clean.[TRAV-KEY], Format([F_ARRV_DAT],"m") AS MonthOrder, 
Final_Clean.C_NUMBER, Final_Clean.F_NUMBER, Final_Clean.F_ARRV_DAT, Final_Clean.F_TIME,
Final_Clean.AD_CODE, Final_Clean.SEX,
Final_Clean.BIRTH_YR, Final_Clean.CNRTY, 
Final_Clean.NATY,Final_Clean.OCCUPATION, Final_Clean.RESIDENCE, Final_Clean.PROVINCE, 
Final_Clean.PORT_ED,
 Final_Clean.ACCOM,
Final_Clean.PURPOSE, Final_Clean.L_O_STAY, 
Final_Clean.POSTAL_CD, [ Country Codes].REGION, [ Country Codes].[REGIONS 2], 
[ Airline Codes].VESSEL_FLIGHTID, [ Airline Codes].VESSEL_FLIGHTNAME, 
[ Airline Codes].COUNTRYOFREGISTRY, [ Airline Codes].TYPE, 
[ Airline Codes].CODE 
AS [ Airline Codes_CODE], 
[ Hotel Codes].[NAME OF PROPERTY], 
[ Hotel Codes].DESCRT AS [ Hotel Codes_DESCRT], 
[ Hotel Codes].EST_NAME, [ Hotel Codes].EST_CODE, 
[ Hotel Codes].[LOCATION CODE], [ Hotel Codes].[CLASSIFICATION CODE], 
[ Hotel Codes].UNITS, [ Hotel Codes].ROOMS, 
[ Hotel Codes].[RATE CODE], [ Hotel Codes].PHONE, 
[ Hotel Codes].[LOCATED ON ACCOM LIST?], [ Job Codes].CODE AS [ Job Codes_CODE], 
[ Job Codes].DESCRT AS [ Job Codes_DESCRT], 
[ Job Codes].OCCUPATION AS [ Job Codes_OCCUPATION], [ Job Codes].OCCODE, 
[ Purpose Codes].Pcode,
 [ Purpose Codes].Purpose2, [ Purpose Codes].Purpose AS [ Purpose Codes_Purpose], 
[ US Zip Codes].POSTAL_CD AS [ US Zip Codes_POSTAL_CD], [ US Zip Codes].STATE, 
[ US Zip Codes].CITY, [ US Zip Codes].STATE_CD, 
[ Hotel Codes].BHTA, [ Hotel Codes].[INTIMATE HOTELS], 
[ Hotel Codes].[REGISTERED ()],
 [ Country Codes].REGIONS3
FROM [ US Zip Codes] RIGHT JOIN ([ Purpose Codes]
 RIGHT JOIN ([ Job Codes] RIGHT JOIN ([ Hotel Codes]
 RIGHT JOIN ([ Country Codes] 
RIGHT JOIN ([ Airline Codes] 
RIGHT JOIN Final_Clean 
ON [ Airline Codes].VESSEL_FLIGHTID = Final_Clean.F_NUMBER)
 ON [ Country Codes].CODE = Final_Clean.RESIDENCE) 
ON [ Hotel Codes].[NAME OF PROPERTY] = Final_Clean.ACCOM) 
ON [ Job Codes].CODE = Final_Clean.OCCUPATION)
 ON [ Purpose Codes].Pcode = Final_Clean.PURPOSE)
 ON [ US Zip Codes].POSTAL_CD = Final_Clean.POSTAL_CD
WHERE (((Final_Clean.RESIDENCE)<>'USA') OR ((Final_Clean.ACCOM)<>'Not Req' 
Or (Final_Clean.ACCOM)<>'cruise' Or (Final_Clean.ACCOM)<>'Not Stated') 
OR ((Final_Clean.PURPOSE)<>'W' Or (Final_Clean.PURPOSE)<>'CO' 
Or (Final_Clean.PURPOSE)<>'CR' Or (Final_Clean.PURPOSE)<>'IC' 
Or (Final_Clean.PURPOSE)<>'EM' Or (Final_Clean.PURPOSE)<>'IP') 
or ((Final_Clean.L_O_STAY)>"0" 
And (Final_Clean.L_O_STAY)<"183"));

The important part is in the Where clause. So if I had a record which had Residence as 'USA' I should not see it in my resultset, neither should I see one where L_O_Stay is greater than 183.

However I am seeing records which should not be present. I know that OR displays a row if any of the conditions listed are true, so I am wondering if this is the issue.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-23 : 17:41:20

Can you do this?

WHERE 	Final_Clean.RESIDENCE <>'USA'
AND	Final_Clean.ACCOM NOT IN ('Not Req','cruise','Not Stated')
AND	Final_Clean.PURPOSE NOT IN '(W','CO','CR','IC','EM','IP') 
AND	Final_Clean.L_O_STAY BETWEEN "1" And "182";

Is Final_Clean.L_O_STAY really a text field because if so then "183" would be less then "2" and <=/>=/or BETWEEN would have no logical meaning in this query.

maybe this instead:

AND	CInt(Final_Clean.L_O_STAY) BETWEEN 1 and 182

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-23 : 18:39:13

Hello,

Yes, that definitely is better. Final_Clean.L_O_STAY is a text field, but when I tried CInt(Final_Clean.L_O_STAY) BETWEEN 1 and 182 I keep getting an Overflow error.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-24 : 09:19:48

How about CLng(Final_Clean.L_O_Stay) BETWEEN 1 and 182?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-24 : 13:47:19

Hello,

Yes, that worked. Thanks a lot for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources