Help with WHERE

Question

Help with WHERE

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
March 22, 2012 at 11:08 am

#100464

Hi there,
I have the following script -
SELECT SALES.UID
,SALES.Reference
--,SALES.SOStatusID
--,SALES.SalesOrderCategoryID
,COUNT(SERV.Reference) AS 'Number Of Service Orders Attached'
FROM dbo.SalesOrders AS SALES
INNER JOIN
dbo.ServiceOrders AS SERV
ONSALES.UID = SERV.DocumentID
WHERE SALES.RecordStatusID = '0'
AND SALES.SOStatusID NOT IN ('7','11','19','17','16','18')
--ANDSALES.SalesOrderCategoryID = '11'
AND[Number Of Service Orders Attached] = '1'
GROUP BY SALES.UID
,SALES.Reference
When i add the following to my WHERE = AND[Number Of Service Orders Attached] = '1'
I begin to get no results which isn't correct. How do I go about adding Number Of Service Orders Attached to a WHERE clause?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Since we don't have access to your database and the data, it is difficult to answer a question the appears on the surface to be a data issue, not a problem with the query itself.

If you could post the DDL for the tables and some sample data that is representative of your problem domain we may be able to assist.

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Looks like you are filtering based on the aggregated COUNT(SERV.Reference) field in the select?

In that case, you need to add a HAVING clause:

SELECT SALES.UID

,SALES.Reference

--,SALES.SOStatusID

--,SALES.SalesOrderCategoryID

,COUNT(SERV.Reference) AS 'Number Of Service Orders Attached'

FROM dbo.SalesOrders AS SALES

INNER JOIN

dbo.ServiceOrders AS SERV

ONSALES.UID = SERV.DocumentID

WHERE SALES.RecordStatusID = '0'

AND SALES.SOStatusID NOT IN ('7','11','19','17','16','18')

--ANDSALES.SalesOrderCategoryID = '11'

GROUP BY SALES.UID

,SALES.Reference

HAVING COUNT(SERV.Reference) = '1'

Is that right?

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Gazareth (3/22/2012)
Looks like you are filtering based on the aggregated COUNT(SERV.Reference) field in the select?
In that case, you need to add a HAVING clause:
SELECT SALES.UID
,SALES.Reference
--,SALES.SOStatusID
--,SALES.SalesOrderCategoryID
,COUNT(SERV.Reference) AS 'Number Of Service Orders Attached'
FROM dbo.SalesOrders AS SALES
INNER JOIN
dbo.ServiceOrders AS SERV
ONSALES.UID = SERV.DocumentID
WHERE SALES.RecordStatusID = '0'
AND SALES.SOStatusID NOT IN ('7','11','19','17','16','18')
--ANDSALES.SalesOrderCategoryID = '11'
GROUP BY SALES.UID
,SALES.Reference
HAVING COUNT(SERV.Reference) = '1'
Is that right?
Thanks

Good catch. I didn't really see the column alias in the select.

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

I didn't at first either. Then wondered why the field name's format was so different to the rest of the fields!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

One other point, it looks like you are referencing numbers with string values. This means that sql has to make an implicit conversion over and over. You should remove the ' when referring to numbers.

SELECT SALES.UID

,SALES.Reference

--,SALES.SOStatusID

--,SALES.SalesOrderCategoryID

,COUNT(SERV.Reference) AS 'Number Of Service Orders Attached'

FROM dbo.SalesOrders AS SALES

INNER JOIN

dbo.ServiceOrders AS SERV

ONSALES.UID = SERV.DocumentID

WHERE SALES.RecordStatusID = 0

AND SALES.SOStatusID NOT IN (7,11,19,17,16,18)

--ANDSALES.SalesOrderCategoryID = 11

GROUP BY SALES.UID

,SALES.Reference

HAVING COUNT(SERV.Reference) = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 6

Hi guys,

HAVING tsql is just what I was after.

Thanks for all your help.