Using a where clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using a where clause

Author

Topic

ryanaud
Starting Member

4 Posts

Posted - 2007-10-30 : 12:40:52

I am currently trying to do some unit reports for work, but I have ran into a little snag. Here is my query...

Select C.ClientName, (SELECT COUNT(WO.WorkOrderID)
FROM WorkOrders WO INNER JOIN
AssemblySequence ASQ WITH(NOLOCK) ON WO.WorkOrderID = ASQ.WorkOrderID INNER JOIN
Assembly A WITH(NOLOCK) ON ASQ.AssemblyID = A.AssmID INNER JOIN
AssemblyInstruction AI WITH(NOLOCK) ON A.AssmID = AI.AssemblyID
WHERE (AI.Name = 'Application Type' AND InstructionValue = 'Double Hung') AND (AI.Name = 'Glass1 Type' AND InstructionValue = 'SSLOE') AND WO.ClientID = C.ClientID) AS DHCount
From SunCenterpoint..Clients C

This returns 0 for every client. I am needing to get the number of units that were Double Hung and SSLOE. I have tried many attempts and many different variations of the query and still no luck. Any help would be appreciative.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-30 : 12:49:28

Does subquery the query return values if you ran it by itself by passing a specific clientid?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

klo
Starting Member

7 Posts

Posted - 2007-10-30 : 13:09:45

[code]
WHERE (AI.Name = 'Application Type'
AND InstructionValue = 'Double Hung')
AND (AI.Name = 'Glass1 Type' AND InstructionValue = 'SSLOE')
[/code]

... whats going on here? .. Replace AND with OR ??

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-30 : 13:15:12

quote:
Originally posted by klo

WHERE (AI.Name = 'Application Type'
AND InstructionValue = 'Double Hung')
AND (AI.Name = 'Glass1 Type' AND InstructionValue = 'SSLOE')

... whats going on here? .. Replace AND with OR ??