Problem with WHERE AND OR - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with WHERE AND OR

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-13 : 23:05:49

I have a query that is partially working.

SELECT     SUM(SLPTRANS.TimeSpent) / 3600 AS Hours, CLIINFOF.AddressLine1
FROM         CLIINFOF INNER JOIN
                      SLPTRANS ON CLIINFOF.RecordID = SLPTRANS.ClientID INNER JOIN
                      CUSTOMC ON CLIINFOF.RecordID = CUSTOMC.RecordIDQ
WHERE     (SLPTRANS.ActyExpID = '417') OR
                      (SLPTRANS.ActyExpID = '418') AND (CUSTOMC.Service_Cont = 'YES')
GROUP BY CLIINFOF.AddressLine1

The 417 is working okay and the service_cont = yes, but it is skipping the 418. Can anyone tell me what I am doing wrong? Thanks!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-13 : 23:10:45

extra pair of parenthesis

WHERE     ((SLPTRANS.ActyExpID = '417') OR
                      (SLPTRANS.ActyExpID = '418')) AND (CUSTOMC.Service_Cont = 'YES')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-13 : 23:49:08

[code]WHERE
SLPTRANS.ActyExpID in ('417','418') AND
CUSTOMC.Service_Cont = 'YES'[/code]

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-13 : 23:59:50

Yes. Simpler and cleaner.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 02:17:24

SUM(SLPTRANS.TimeSpent) / 3600.0 AS Hours

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-14 : 02:55:58

quote:
Originally posted by Peso

SUM(SLPTRANS.TimeSpent) / 3600.0 AS Hours

Peter Larsson
Helsingborg, Sweden

If a variable is used in place of 3600, then

1.0*SUM(SLPTRANS.TimeSpent) / @var

Madhivanan

Failing to plan is Planning to fail

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 07:58:43

Okay, I don't want to sound crazy, but here goes.

If I try this:

SELECT     SUM(dbo.SLPTRANS.TimeSpent) / 3600 AS Hours, dbo.CLIINFOF.AddressLine1
FROM         dbo.CLIINFOF INNER JOIN
                      dbo.SLPTRANS ON dbo.CLIINFOF.RecordID = dbo.SLPTRANS.ClientID INNER JOIN
                      dbo.CUSTOMC ON dbo.CLIINFOF.RecordID = dbo.CUSTOMC.RecordIDQ
WHERE     (dbo.SLPTRANS.ActyExpID = '417') OR
                      (dbo.SLPTRANS.ActyExpID = '418') AND (dbo.CUSTOMC.Service_Cont = 'YES')
GROUP BY dbo.CLIINFOF.AddressLine1

I get the results that have 417

If I try this:

SELECT     SUM(dbo.SLPTRANS.TimeSpent) / 3600 AS Hours, dbo.CLIINFOF.AddressLine1
FROM         dbo.CLIINFOF INNER JOIN
                      dbo.SLPTRANS ON dbo.CLIINFOF.RecordID = dbo.SLPTRANS.ClientID INNER JOIN
                      dbo.CUSTOMC ON dbo.CLIINFOF.RecordID = dbo.CUSTOMC.RecordIDQ
WHERE     (dbo.SLPTRANS.ActyExpID = '418') OR
                      (dbo.SLPTRANS.ActyExpID = '417') AND (dbo.CUSTOMC.Service_Cont = 'YES')
GROUP BY dbo.CLIINFOF.AddressLine1

I get results for 418

If I try this:

WHERE     ((SLPTRANS.ActyExpID = '417') OR
                      (SLPTRANS.ActyExpID = '418')) AND (CUSTOMC.Service_Cont = 'YES')

Or This:

WHERE
	SLPTRANS.ActyExpID in ('417','418')	AND
	CUSTOMC.Service_Cont = 'YES'

I get no results returned

I really appreciate your help so far, but am really confused on this one.

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 08:14:09

SELECT
(SUM(dbo.SLPTRANS.TimeSpent) / 3600.0) AS Hours
,dbo.CLIINFOF.AddressLine1
FROM
dbo.CLIINFOF
INNER JOIN
dbo.SLPTRANS
ON dbo.CLIINFOF.RecordID = dbo.SLPTRANS.ClientID
INNER JOIN
dbo.CUSTOMC
ON dbo.CLIINFOF.RecordID = dbo.CUSTOMC.RecordIDQ
WHERE
dbo.SLPTRANS.ActyExpID IN ('418','417')
AND (dbo.CUSTOMC.Service_Cont = 'YES')
GROUP BY
dbo.CLIINFOF.AddressLine1

Try This!

Necessity is the mother of all inventions!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:17:50

Please do not make us guess more!
Tell us the logic with ActyExpID and Service_Cont.

Do you want all records where Service_Cont equals 'Yes' AND ActyExpID is either 417 or 418?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:19:32

As you can see, by using paranthesis you can get up to 8 different solutions!

Peter Larsson
Helsingborg, Sweden

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 08:32:52

I have three tables:

CLINFOF
SLIPTRANS
CUSTOMC

CLINFOF Record ID
SLPTRANS Client ID
CUSTOMC RecordIDQ

These are the items that relate between all the tables.

CUSTOMC has a column called Service_Cont which has YES or NO
SLPTRANS has a column called TimeSpent with time saved in seconds

I am trying to pull customer CLINFOF.AddressLine1 and SLPTRANS.TimeSpent in hours Where CUSTOMC.Service_Cont = Yes and SLPTRANS.ActyExpID = 417 or 418 and group it by CLINFOF.AddressLine1

I hope this explains what I am trying to do a little better. Sorry about any confusion.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:42:36

Please fill in all combinations possible for Service_Cont and ActyExpID

ActyExpID	Service_Cont		Result
---------	------------		------
111		Yes			?
111		No			?
417		Yes			?
417		No			?
418		Yes			?
418		No			?

Is result OK or not ok? Copy and paste and replace question mark with a '1' if line combination should return a record, and '0' if line combination should not return a record.

Peter Larsson
Helsingborg, Sweden

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 09:03:15

I have another question to add to this .. why do you to use WHERE , when you use it in the JOIN condition itself?

SELECT
(SUM(dbo.SLPTRANS.TimeSpent) / 3600.0) AS Hours
,dbo.CLIINFOF.AddressLine1
FROM
dbo.CLIINFOF
INNER JOIN
dbo.SLPTRANS
ON dbo.CLIINFOF.RecordID = dbo.SLPTRANS.ClientID
AND dbo.SLPTRANS.ActyExpID IN ('418','417')
INNER JOIN
dbo.CUSTOMC
ON dbo.CLIINFOF.RecordID = dbo.CUSTOMC.RecordIDQ
AND (dbo.CUSTOMC.Service_Cont = 'YES')
GROUP BY
dbo.CLIINFOF.AddressLine1

Necessity is the mother of all inventions!

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 09:07:11

ActyExpID Service_Cont Result
--------- ------------ ------
111 Yes 0
111 No 0
417 Yes 1
417 No 0
418 Yes 1
418 No 0

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 09:32:33

Richard , didnt u still get the required results from the above query? ;-0

Necessity is the mother of all inventions!

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 09:37:57

Yeah, it still comes back blank with your query. I don't get it. If it comes back with one or the other, why does it come back blank with an or.

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 09:42:20

If it coming back with a blank .. that means there no data matching the condition ..or u are doing something wrong somewhere. Can u post the query u are executing?

Necessity is the mother of all inventions!

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 09:49:44

I copied and pasted your exact query. I am really at a loss.

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-05-14 : 09:56:21

"ON dbo.CLIINFOF.RecordID = dbo.SLPTRANS.ClientID"
I think this might be ur problem.. check if this is the correct condition to join the 2 tables on.

Necessity is the mother of all inventions!

dprichard
Yak Posting Veteran

94 Posts

Posted - 2007-05-14 : 10:03:55

Good call... I am so sorry... I went based on what the guy who setup the table told me and he said that the client was a service contract client. When I looked through the data he was incorrect. I am really sorry to have put you guys through all this trouble.

Please don't blacklist me. I will be more careful in the future!!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-14 : 10:07:57

"Please don't blacklist me."

You mean Twit-List?
Barely missed it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources