| Author |
Topic |
|
binggeli
Starting Member
20 Posts |
 Posted - 2005-10-28 : 15:30:29
|
| In trying to retrieve all employees who share the same position ID, I am running into serious problems when trying to join another table.For starters, here is how I determine how many records there are:SELECT COUNT(*) FROM User WHERE PositionID IN (3431,3432,3433,3434,3437,3632,3874,3876,3877,4052,4243,4245,4292,4450,4677,4678,4679,4680,4707,4709,4710,4711,4712,4713,4717,4721,4722,4723,4724,4725,4726,4727,4728,4730,4731,4732,4733,6071,6073,6074,6075,6435,6439,6616,6799,6801,8425,8433,9318,9770,9836,9844,9889,12662,15110,15236,15237,15238,15239,15240,16665,17065,17071,18226,21061,21800,21801,23339,23342,23343,23344,23345,24682,26231,27343,28813,31499,32492,37580,37994,42077,42078,44803,48756,49521,50034,50299,50300,50301,50302,52434,53177,53798,53799,54687,54708,57742,58053,58319,58320,58321,58322)AND ClientID = 'GOOBER'-- returns 6776 rowsFor reasons about which I cannot do anything, there is some related information in another table for each user -- more or less, but it could also be that there is no information available.So, I used a LEFT OUTER JOIN to produce this query (which runs inside a LOOP, grabbing position id values (@c) from a table variable (@q):IF (@type = 'position' AND @coll > 0)BEGINPOSITION_INFO:-- -----------------------------------------------------------------------IF (@mCount > 0) GOTO GET_POSITION_INFO ELSE GOTO END_POSITION_INFO-- -----------------------------------------------------------------------GET_POSITION_INFO: SET @i = (SELECT TOP 1 mIdt FROM @q WHERE mLoop_01 = 0) SET @c = (SELECT mPositionId FROM @q WHERE mIdt = @i)-- ----------------------------------------------------------------------- IF (@c > 0 AND @c IS NOT NULL) BEGIN-- -----------------------------------------------------------------------INSERT INTO @q (mUserId, mAppraiserId) SELECT u.UserID, p.AppraiserID FROM User u LEFT OUTER JOIN Appraisers p ON u.UserID = p.UserID WHERE u.PositionID = @c AND u.ClientID = @client_id AND p.Level = 1 END-- ----------------------------------------------------------------------- UPDATE @q SET mLoop_01 = 7 WHERE mIdt = @i SET @c = 0 SET @i = 0 SET @mCount = (@mCount - 1) IF (@mCount > 0) GOTO GET_POSITION_INFO ELSE GOTO END_POSITION_INFO-- -----------------------------------------------------------------------END_POSITION_INFO:-- -----------------------------------------------------------------------END-- returns 6431 rowsHowever, doing so only returns 6,431 rows. What am I doing wrong?Can anybody point me to any resources or point out any thinkos (thinking flaws)? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-28 : 15:42:43
|
| SQL 101: Never put criteria in your WHERE clause on an outer-joined table.That turns your OUTER JOIN into an inner join, since Nulls are no longer handled.Two options:select ...from aleft outer join (select * from b where <criteria for table b here>) tmpon ...where ...ORselect ...from aleft outer join b on .... AND <criteria for table a here>where ... |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-28 : 15:44:04
|
| more info: http://www.sqlteam.com/item.asp?ItemID=11122 |
|
|
|
binggeli
Starting Member
20 Posts |
Posted - 2005-10-28 : 15:50:20
|
| Wow! I had no idea. Thank you very much.I made the following change to my LEFT OUTER JOIN:LEFT OUTER JOIN Perf_Appraisers p ON u.UserID = p.UserID AND p.AppraiserLevel = 1But a funny thing happened on the way to the forum: Now I only get 6,422 rows returned. Off I go, checking out your link, but I thought I'd pass this on as a half time result (been at this for hours). |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-10-28 : 16:06:42
|
| By the way -- what the heck are you doing with that loop? You should have no need for it. Why don't you tell us what you are trying to accomplish with that loop, give us some sample data, and what the results should be based on that sample data with an brief explaination of the process. |
|
|
|
binggeli
Starting Member
20 Posts |
Posted - 2005-10-28 : 16:17:48
|
| Yes, I know, there's a lot of implied stuff here. So, let me briefly explain:-- Some manager wants to run a report and include all employees that hold a certain position (or belong to a certain department, division, location, etc.)-- I get one or up to 5,000 IDs of stuff from the web page. As I mentioned, this could be position id's, location id's, department id's, etc.-- In my Stored Procedure, I parse those comma-separated ID's and put them into a table variable that will server as a novice SQL programmer's hash table. (By the way, rather than messing with TEXT I am overloading a bunch of VARCHAR parameters, as that seems to go much smoother when processing the comma-separated lists of ID's.)-- There is also a @type parameter that identifies what I am dealing with (i.e., positions, locations, departments, etc.).And the loop does the following:1) Grab the first "thingy" ID from the table variable2) INSERT all employees who belong to the position, department, location, etc. into that table variable.3) Ideally get all associated employee information from other tables as well while I'm doing that.4) When I'm done with the current ID, set the loop field in the table variable to a unique value (such as 7) so that this ID does not get selected again. Or just delete that row and move on.When all of that is done, the Stored Procedure returns the contents of that table variable as the result.Perhaps I am missing something, 'cause it sounds as though you may have a better idea than looping. I am not too savvy (obviously) with "all things T-SQL" yet, but I'm not afraid of learning new things and putting in the sweat equity.Thanks. |
|
|
|
binggeli
Starting Member
20 Posts |
Posted - 2005-10-28 : 17:20:37
|
| Thank you "jsmith8858" for your help. I read your first reply more carefully and made some adjustments. Everything is working now. |
|
|
|
|
|
|
LEFT OUTER JOIN returns fewer rows...