SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simplifying "OR" and "<>" in a query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mizi
Starting Member

2 Posts
Posted - 05/03/2012 :  06:17:24  Show Profile  Reply with Quote
I was wondering if there is a 'cleaner' or neat way to arrange this SQL query without repeating the same statement so many times but getting the 'wanted' result?

Wanted SQL query:
select * from Jenayah_Kekerasan 
where n_balai='BP Pulau Tikus' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012

OR n_balai='BP Jelutong' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012

OR n_balai='BP Bandar Baru' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012

OR n_balai='BP Jalan Patani' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012

OR n_balai='BP Bayan Baru' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012

OR n_balai='BP Bukit Mertajam' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012



this is what i tried to simplified that query but didn't get the same result as 'wanted' query:
select * from Jenayah_Kekerasan 
where n_balai='BP Pulau Tikus' OR n_balai='BP Jelutong' OR n_balai='BP Bandar Baru' OR n_balai='BP Jalan Patani' OR n_balai='BP Bayan Baru' OR n_balai='BP Bukit Mertajam' 
and Jenis <> 'Cabul Kehormatan' and Jenis <> 'Merusuh' and Jenis <> 'Pemerasan' and Jenis <> 'Ugutan Jenayah' 
and YEAR(T_Mula)=2012


I wonder where i did wrong?
thanks in advance :)

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 05/03/2012 :  07:08:29  Show Profile  Reply with Quote
Try 
n_balai IN ('BP Bukit Mertajam' , 'BP Bayan Baru' ...) AND Jenis <> ....










How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 05/03/2012 :  07:09:31  Show Profile  Reply with Quote
You need to put a few brackets to make the precedence clear. http://msdn.microsoft.com/en-us/library/ms190276.aspx

May be this is what you want?
select * from Jenayah_Kekerasan 
where n_balai IN ('BP Pulau Tikus' , 'BP Jelutong','BP Bandar Baru')
AND Jenis NOT IN ('Cabul Kehormatan','Merusuh','Pemerasan' ,'Ugutan Jenayah')
and YEAR(T_Mula)=2012
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 05/03/2012 :  08:05:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
SELECT	*
FROM	dbo.Jenayah_Kekerasan 
WHERE	n_balai IN ('BP Pulau Tikus', 'BP Jelutong', 'BP Bandar Baru', 'BP Jalan Patani', 'BP Bayan Baru', 'BP Bukit Mertajam')
	AND Jenis NOT IN ('Cabul Kehormatan', 'Merusuh', 'Pemerasan', 'Ugutan Jenayah')
	AND T_Mula >= '20120101'
	AND T_Mula < '20130101'


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Mizi
Starting Member

2 Posts
Posted - 05/03/2012 :  22:05:15  Show Profile  Reply with Quote
yeah putting them IN brackets works! I never thought it was possible to do it that way. thank you very much.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000