March 31, 2016 at 11:22 am
Thank you both. Great forum!
March 31, 2016 at 11:58 am
Thanks. Couldn't do the same 'Where IN' using
March 31, 2016 at 12:05 pm
Sorry. Hit go too soon. To continue,
couldn't I use the same 'Where In' clause to qualify (filter) 'Sales'.empID using 'WorkForce' empID? Workforce.empID lists unique ID's. If so, can you use two 'Where IN's along with the two Joins? How, if so.
March 31, 2016 at 12:11 pm
still waiting for some sample scripts....are these on the way?
does your "sales" table have multiple entries for same employee? (I am guessing yes??)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 31, 2016 at 12:28 pm
doasidont (3/31/2016)
Sorry. Hit go too soon. To continue,couldn't I use the same 'Where In' clause to qualify (filter) 'Sales'.empID using 'WorkForce' empID? Workforce.empID lists unique ID's. If so, can you use two 'Where IN's along with the two Joins? How, if so.
Both the ON clause and the WHERE clause take Boolean expressions. An IN clause is a Boolean expression.
It really, really, REALLY helps to learn how to read the BNF descriptions of t-sql objects.
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)
This says that the format of an IN expressions is a test_expression, an optional NOT, a required IN, followed by either a subquery (that returns a single column) or a list of expressions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2016 at 3:16 pm
Luis Cazares (3/31/2016)
Which is equivalent to this:
SELECT WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType,
Sum(convert(int,Sales.salesTot)) AS [SumOfsalesTot] --This wass missing a parenthesis
FROM WorkForce
INNER JOIN Sales ON WorkForce.empID = Sales.empID --You had an additional parenthesis in here.
INNER JOIN Products ON Sales.prodID = Products.prodID
INNER JOIN PayTypes ON Sales.payID = PayTypes.payID
WHERE WorkForce.zip IN
(SELECT ZipTable.zip
FROM ZipTable)
GROUP BY WorkForce.empID,
WorkForce.empName,
WorkForce.zip,
Sales.prodID,
PayTypes.payType,
Products.prodType
ORDER BY [SumOfsalesTot] DESC; --Use column alias in your ORDER BY clause.
No, it's not fully equivalent. NULL values in the subquery have a subtle effect on this query, which becomes far less subtle when you negate the condition.
Additionally, the EXISTS version will still work if you have to compare more than a single column; the IN version will not.
In short, EXISTS works under all curcumstances, IN only under some.
For that reason, I only ever use [NOT] IN with a list of constant expressions, never with a subquery - I always use [NOT] EXISTS for that.
March 31, 2016 at 3:17 pm
doasidont (3/31/2016)
Sorry. Hit go too soon. To continue,couldn't I use the same 'Where In' clause to qualify (filter) 'Sales'.empID using 'WorkForce' empID? Workforce.empID lists unique ID's. If so, can you use two 'Where IN's along with the two Joins? How, if so.
Since you use a column from the Sales table, you need to include that table in the JOIN.
April 5, 2016 at 6:11 am
Thanks again to all. This topic closed.
April 5, 2016 at 11:13 am
doasidont (4/5/2016)
Thanks again to all. This topic closed.
Not necessarily. Someone else may come along at some future date, look at the solution provided and decide to see if there may be a better way. That is the nature of these forums, and why many of us like ssc.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply