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
 General SQL Server Forums
 New to SQL Server Programming
 Basic Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jrobin747
Starting Member

USA
48 Posts

Posted - 07/05/2013 :  12:19:49  Show Profile  Reply with Quote
I'm new to SQL
I'm SQL Server Manager 2008
I have to write a query that gets a list of all the records from DataLinkMerhcants that have an ApplicationID <>0 where Assignedto is NULL or is 0

I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the query

This is what typed
SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedTo
FROM dbo.DatalinkMerchant
WHERE ApplicationID <>0 AND AssignedTo=NULL OR 0

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/05/2013 :  12:24:52  Show Profile  Reply with Quote


SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedTo
FROM dbo.DatalinkMerchant
WHERE ApplicationID <>0 AND (AssignedTo IS NULL OR 
AssignedTo = 0)


quote:
Originally posted by jrobin747

I'm new to SQL
I'm SQL Server Manager 2008
I have to write a query that gets a list of all the records from DataLinkMerhcants that have an ApplicationID <>0 where Assignedto is NULL or is 0

I'm doing something incorrectly. I get a red squiggly line under the last 0 (zero) in the query

This is what typed
SELECT dbo.DatalinkMerchant.ApplicationID,dbo.DatalinkMerchant.AssignedTo
FROM dbo.DatalinkMerchant
WHERE ApplicationID <>0 AND AssignedTo=NULL OR 0

Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 07/05/2013 :  12:49:51  Show Profile  Reply with Quote
So I understand, why did you use parenthesis? Did you have to? Could it have been done another way. This is new to me.

(AssignedTo IS NULL OR
AssignedTo = 0)

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/05/2013 :  13:01:38  Show Profile  Reply with Quote
This has to do with the operator precedence. AND Has a higher precedence than OR - see here: http://msdn.microsoft.com/en-us/library/ms190276.aspx What that means is that if you don't have paranthesis, SQL Server will first evluate the "ApplicationID <>0 AND AssignedTo=NULL" . Then it will use the result of that operation an OR it with 0. Using the paranthesis forces the OR operation to be evaluated first.

So whether you need to use paranthesis or not depends on the logic you are trying to implement.
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 07/05/2013 :  14:44:08  Show Profile  Reply with Quote
Just to add to James K comments. The () puts thing in order on how it is evaluated. For example.

Think of Algebra.
4 * (9 + 3) = 48
4 * 9 + 3 = 39

In Algebra, the equation in () are done first thus the two different answers shown above. This is how I visualize in determining if and when I need to use ().
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/05/2013 :  15:01:14  Show Profile  Reply with Quote
quote:
Originally posted by ugh3012

Just to add to James K comments. The () puts thing in order on how it is evaluated. For example.

Think of Algebra.
4 * (9 + 3) = 48
4 * 9 + 3 = 39

In Algebra, the equation in () are done first thus the two different answers shown above. This is how I visualize in determining if and when I need to use ().


That is a very good explanation! That is what I should have said instead of talking about operator precedence and such crap
Go to Top of Page

jrobin747
Starting Member

USA
48 Posts

Posted - 07/05/2013 :  15:07:16  Show Profile  Reply with Quote
Thanks so much.
From a math perspective it makes all the sense in the word. As newbie programmer, based on my assignment to write the query I would have no idea what order should be taken. I ran the query with and without quotes and got the same answer 83 records.

I guess the other hard thing in learning this stuff is somethimes even if I received the message "Query executed successfully" I wouldn't know if I ACTUALLY got the proper results or not.

Yes in the temporary table ApplicationID records have a number <>0 and the AssignedTo records either have 0 or NULL. So I'm guessing all is well.

I'm told it will make sense eventually.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 07/05/2013 :  16:06:03  Show Profile  Reply with Quote
quote:
Originally posted by jrobin747

Thanks so much.
From a math perspective it makes all the sense in the word. As newbie programmer, based on my assignment to write the query I would have no idea what order should be taken. I ran the query with and without quotes and got the same answer 83 records.

I guess the other hard thing in learning this stuff is somethimes even if I received the message "Query executed successfully" I wouldn't know if I ACTUALLY got the proper results or not.

Yes in the temporary table ApplicationID records have a number <>0 and the AssignedTo records either have 0 or NULL. So I'm guessing all is well.

I'm told it will make sense eventually.

Did you mean with and without the brackets? That doesn't sound quite right, unless the data happened to be such, which doesn't seem very likely - but you never know.

The way to debug it is to look at a subset of the data that you can manually examine and compare with what the query returns. So, for example you might add another where clause:
WHERE ApplicationID<>0 AND (AssignedTo IS NULL OR AssignedTo = 0)
AND ApplicationID = 12345
Now look at all the rows from the table that have ApplicationId = 12345 and manually examine each row and compare with the results from the query. If AssignedTo is not null and it is not zero, then that row should not be there, if it is NULL it should there etc.
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.08 seconds. Powered By: Snitz Forums 2000