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.
| Author |
Topic |
|
Crumpelfungus
Starting Member
5 Posts |
 Posted - 2005-06-08 : 10:28:20
|
| Somebody give me a dope slap, please!-- SQL Server 2000/SP4-- Win2KAll I want to do, is join two tables to get my result set of 14 records returned correctly.If I do this: SELECT factor_id FROM pos WHERE position_id = 39985it returns 14 records, and that's correct.If I do this: SELECT pos_fac_id, pos_fac_score FROM facs it returns 14 records, and that's correct.So, since the "pos" table contains additional information (rating, weight percentage, etc.) for the individual entries in the "facs" table, I need to perform a join to get the complete picture, so to speak.If I do this: SELECT a.pos_fac_id, a.pos_score, b.fac_weight FROM facs AS a, pos AS b WHERE a.pos_fac_id = b.pos_fac_id AND a.appr_id = 4918 AND a.pos_fac_id IN (SELECT pos_fac_id FROM pos WHERE position_id = 9795) AND a.client_id = 'ABC'then it returns 140 records.Additionally, I tried adding DISTINCT and that reduced the result set to 36.The JOIN syntax seems to make no difference (tried INNER JOIN), and I'm afraid there's something more substantial I am missing or misunderstanding.Could somebody point me in the right direction, please?Thank you. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-06-08 : 10:30:45
|
| Follow the direction below my sig.We need the table DDL, inclusing the primary key info...But I'd say you're getting a cartesian productBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
NoNulls
Starting Member
14 Posts |
Posted - 2005-06-08 : 10:53:15
|
| Quick suggestion without knowing details of the tables.I like to do a quick left and/or right just to see what is possibly missing between the two tables.i.e.SELECT *FROM facs AS aright join pos AS b on (a.pos_fac_id = b.pos_fac_id) |
|
|
|
Crumpelfungus
Starting Member
5 Posts |
Posted - 2005-06-08 : 11:01:41
|
Phew . . . here's the info to accompany my initial query.1) There are no relationships in this database. This is how I inherited it from the Senior Developer.2) Some of the table and field names had to be globally searched-and-replaced to protect certain information.Additionally, I was able to make some adjustments to my query and the JOIN (see below), but I am still not getting the "correct" result set -- which should be at least 13 records.
CREATE TABLE [facs] ( [app_fac_id] [int] IDENTITY (1, 1) NOT NULL , [app_id] [int] NULL , [fac_id] [int] NULL , [f_weight] [int] NULL , [f_score] [float] NULL , [f_s_score] [float] NULL , [cmnt] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [s_cmnt] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [f_name] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [f_desc] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lvl1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lvl2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lvl3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lvl4] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Lvl5] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ClientID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m2_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m3_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m4_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m5_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m6_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m7_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m8_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m9_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m10_cmnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [m2_score] [float] NULL , [m3_score] [float] NULL , [m4_score] [float] NULL , [m5_score] [float] NULL , [m6_score] [float] NULL , [m7_score] [float] NULL , [m8_score] [float] NULL , [m9_score] [float] NULL , [m10_score] [float] NULL , CONSTRAINT [PK_facs_1] PRIMARY KEY NONCLUSTERED ( [app_fac_id] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [pos] ( [pos_fac_id] [int] IDENTITY (1, 1) NOT NULL , [pos_id] [int] NULL , [fac_id] [int] NULL , [f_weight] [int] NULL , [ClientID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_pos_1] PRIMARY KEY NONCLUSTERED ( [pos_fac_id] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY]GOSELECT * FROM pos WHERE pos_id = 39985-- returns 14SELECT * FROM facs WHERE app_id = 94108-- returns 13SELECT a.f_weight, b.*FROM pos AS a INNER JOIN facs AS b ON a.fac_id = b.fac_idWHERE a.pos_id = 39985AND b.ClientID = 'BGBCPART'AND b.app_id = 94108-- returns 8f_weight app_fac_id app_id fac_id f_weight f_score f_s_score cmnt s_cmnt f_name f_desc Lvl1 Lvl2 Lvl3 Lvl4 Lvl5 msrepl_tran_version ClientID m2_cmnt m3_cmnt m4_cmnt m5_cmnt m6_cmnt m7_cmnt m8_cmnt m9_cmnt m10_cmnt m2_score m3_score m4_score m5_score m6_score m7_score m8_score m9_score m10_score ----------- ------------ ----------- ----------- ----------- ----------------------------------------------------- ----------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 10 644075 94108 254809 5 0.0 0.0 NULL NULL Leadership Ability to effectively lead others in achieving goals and objectives. The degree to which the employee demonstrates a positive example in leading personnel toward Firm goals and objectives. Ability to guide others is unacceptable due to lack of effort, desire, vision, positive attitude, or is distinctly a follower. Improvement is needed. Leadership skills are inconsistent with Firm goals and objectives. Ability to guide others is limited due to lack of effort, desire, viision, or being more comfortable as a follower. Improvement is needed. Leadership skills are inconsistent with Firm goals and objectives. Focus and direction is good but at times is inconsistent. Generally encourages and mentors but some progress is needed. Understands Firm goals and objectives and demonstrates a positive attitude toward them. Promotes Firm goals and objectives to others. Encourages and mentors on a consistent basis. Regarded as an effective leader. Seen as an exemplary role model, contributor, and leader. Holds self and others accountable to high standards. Inspires others to higher levels of performance. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644084 94108 254811 4 0.0 0.0 NULL NULL Practice Development The degree to which results are demonstrated in developing new business. Active in business, social and/or charitable organizations in the community to develop new contacts and promote the Firm. The degree to which actual results are achieved with client Rarely promotes the Firm to prospective clients, referral sources and/or cross-sells to existing clients. Results are not evident or very rare. Occasionally promotes the Firm to prospective clients, referral sources and/or cross-sells to existing clients and referral sources. Tangible results are limited and inconsistent. Frequently promotes Firm to prospective clients, referral sources and/or cross-sells existing clients with tangible results. Consistently pursues new business opportunities, referral sources and cross-selling opportunities. Results in generating tangible new business and referral sources are consistently above average. Outstanding development of business and referral sources with constant effort. Results in generating new business, new referral sources and cross-selling are exceptional. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644081 94108 254829 5 0.0 0.0 NULL NULL Client Relations The degree in developing and maintaining long-term client relationships that are strategic [versus transactional], based on shared goals and which build loyalty to the Firm. The degree to which internal and external client service focus and application is Client relations performance is unacceptable regardless of reason. Complaints, curtness, or lack of concern is common. Internal and external client relations could improve. Sometimes appears too casual or uninterested in client. Projects are frequently completed late. Improvement needed. Internal and external client relation skills are generally at an acceptable level. Attentive, concerned, and polite. Not condescending or abrupt. Generally timely in completion and delivery of service. Excellent client relations skills are demonstrated. Frequently goes above and beyond the requirements of the job when appropriate. Able to diffuse difficult situations and turn into positive. Frequently delivers service on time. Well-refined skills are reflected in consistent and superior internal and external client relations. A constant inspiration to associates in the delivery of internal and external client service and the proper handling of difficult situations. Consistent NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644076 94108 254836 9 0.0 0.0 NULL NULL Communication Ability to effectively converse and listen to others. The use of proper written and grammatical skills and proper use of computer technology [e-mail, internet, etc.]. Communication skills are inadequate for the job. Immediate improvement is necessary in one or more key areas [listening, speaking, writing, using communication technology]. Communication skills are lacking in some areas. Verbal communication skills of listening and speaking clearly need to be improved. More effective use of communication technologies is needed. Written documents need to be stronger. Communication skills are acceptable. Listening skills are adequate to complete the job. Effectively uses some available communication technology. Writiing skills and documents generated are adequate. Communication skills are excellent. Listens effectively. Interpersonal skills are above average. Effectively uses most available communication technology. Written documents are generally effective. Written correspondence is clear and concise and appropr Communication skills are superior. Effectively listens. Interpersonal communication skills strengthen others. Effectivley uses all available communication technology. Written correspondence is clear and concise and appropriate for the audience. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644079 94108 254837 5 0.0 0.0 NULL NULL Adaptability Openness to change and new ideas. Ability to adjust to changing work requirements and respond to new situations. Reluctant or unable to adapt to new situations. Frequently resists change causing problems and disruptive delays. Demonstrates a general resistance to change. Can adapt to new situations, but only when forced. Generally not resistant to change and can adapt to many situations requiring a detour from established procedures if given reasonable time to adapt. Adaptable to most situations or changes. Supportive and proactive in effecting changes. Fits well into virtually any assignment. Not afraid of change, in fact seeks to effectively assimilate change into productive processes. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644082 94108 254845 5 0.0 0.0 NULL NULL Delegation The degree in which the talents of others are enlisted to help meet objectives by giving them important activities and sufficient autonomy to exercise their own judgment. Includes motivating employees to think and work independently. Degree to which the Ineffective in passing duties or funtions to others. Demonstrates desire to do all aspects of assignments individually. Sometimes reticent to delegate work or assignments to others. Occasionally has difficulty in giving other employees autonomy to complete job. Tends to hold on to workload rather than assign to others Generally effective in delegating responsibility and authority to others. Helps mentor, but is careful to not inhibit employee growth. Frequently identifies and utilizes opportunities to leverage self. Consistently demonstrates effective skills in delegation by motivating employees to exercise their own judgment in assigned areas of responsibility. Autonomy is given effectively to build employee skills. Consistent at identification of work to be delega Excellent skills in delegation and motivating employees to higher levels of achievement through outstanding mentoring. Develops employee self worth and the ability to effectively work autonomously. Effectively leverages his or herself. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644077 94108 254798 9 0.0 0.0 NULL NULL Teamwork & Cooperation The degree to which individuals promote a positive, productive, and professional working environment. Demonstrates sensitivity, cooperation, understanding of others, and respect. The degree of synergy promoted. Always lacks willingness to work harmoniously with others. Detracts from a consistent productive work environment. Sometimes lacks willingness to work harmoniously with others. Detracts from a consistent productive work environment. Generally cooperative and willing to work as a contributing team member promoting productivity and professionalism. Willingly communicates and shares information when asked. Eager, willing, and effective in creating a positive team atmosphere. Strives to cooperate and tactfully assist others. Understands the role of a team player. Excellent team player who creates synergy and greatly enhances productivity. Facilitates teamwork beyond the scope of defined functional areas. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL10 644085 94108 254804 9 0.0 0.0 NULL NULL Productivity The level of work output or productivity as compared to a standard or compared to others, free from mistakes, errors or negativity. Very seldom produces a sufficient volume of work for allotted time or effort/ work volume is inconsistent. Quality is consistently below accepted standards. Errors and mistakes are excessive. Does not typically produce a sufficient volume of work for allotted time or effort. Work volume is often inconsistent. Quality occasionally falls below accepted standards. Generally produces expected volumes of work. Errors and mistakes may occur occasionally but are not excessive and are corrected in a timely manner. A desire to produce quality work is demonstrated. When compared to others, clearly above standard in production. Work is clearly above average in terms of consistency and volume and always conscious of the final product.. Consistently produces a high volume of work. Performs work without sacrificing efficiency or economy. Rarely, if ever, misses deadlines or scheduled results. Virtually no mistakes or errors of any consequence. NULL BGBCPART NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL(8 row(s) affected) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-08 : 11:08:39
|
Brett's right. We need more information, but I want to take a crack before I split from my office.quote:
Originally posted by Crumpelfungus
SELECT factor_id FROM pos WHERE position_id = 39985it returns 14 records, and that's correct.If I do this: SELECT pos_fac_id, pos_fac_score FROM facs it returns 14 records, and that's correct.If I do this: SELECT a.pos_fac_id, a.pos_score, b.fac_weight FROM facs AS a, pos AS b WHERE a.pos_fac_id = b.pos_fac_id AND a.appr_id = 4918 AND a.pos_fac_id IN (SELECT pos_fac_id FROM pos WHERE position_id = 9795) AND a.client_id = 'ABC'then it returns 140 records.
I'm having trouble understanding why the 3rd query, which has a completely different WHERE condition than either of the first two queries, should not show 140 rows?Sample data would help. |
|
|
|
Crumpelfungus
Starting Member
5 Posts |
Posted - 2005-06-08 : 11:29:53
|
| OKAY, Let me try to shed a little more light on this. And please forgive me for being so ignorant about providing more and better information (I've been dealing with this and similar issues for days and seem to have blinders on...not a good thing, I admit that freely.)This portion of the HR system tracks every employees position.Each position can have any number of factors associated with it.When a manager begins appraising the performance of an employee, an appraisal reecord is created. That appraisal record points to the employee's position (and the position points to the individual factors).Sadly, there were absolutely no relationships in this database when I started working on it. Similar issues affect indexes and other commonly expected db stuff, but I digress.In an appraisal, the overall factor score requires the sub-totals from all individual factors. To figure out how many and which factors are assigned to the position, I can query like so:SELECT pos_factor_id FROM pos WHERE position_id = 39985Right now, this returns 14 records.To get the rest of the information (the weight that's assigned to the factor, and the score that the manager and up to 9 other people could provide for each individual factor), I need to find:a) all factors that have been assigned to this positionANDb) the scores and weights for each factorIf I have committed some kind of Cartesian blooper, could somebody point out to me where my think-o happens?Thanks much. |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-06-08 : 11:32:47
|
When you do a join that doesnt mean you are going to get the same exact number of records from the table of your original primary key, that just does not make sense, and I hope you understand why. Unless it is a one to one relationship and you're just pulling an attribute from another table?SELECT t1.ID, t2.field1 FROM YourTable INNER JOIN YourOtherTable t2 ON t2.ID = t1.IDIf t2 (YourOtherTable) is the n side in a one to many of course you will get more than just those original records from t1. If t2 is just a one to one correspondence with t1 then you should only get the additional attributes that match t1.ID=t2.ID...But reading your description of the problem, you yourself state that the database has no relations (not normalized???), I would question the data at hand and not the JOIN.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-06-08 : 11:37:20
|
quote:
Originally posted by Crumpelfungus
OKAY, Let me try to shed a little more light on this. And please forgive me for being so ignorant about providing more and better information (I've been dealing with this and similar issues for days and seem to have blinders on...not a good thing, I admit that freely.)This portion of the HR system tracks every employees position.Each position can have any number of factors associated with it.When a manager begins appraising the performance of an employee, an appraisal reecord is created. That appraisal record points to the employee's position (and the position points to the individual factors).Sadly, there were absolutely no relationships in this database when I started working on it. Similar issues affect indexes and other commonly expected db stuff, but I digress.In an appraisal, the overall factor score requires the sub-totals from all individual factors. To figure out how many and which factors are assigned to the position, I can query like so:SELECT pos_factor_id FROM pos WHERE position_id = 39985Right now, this returns 14 records.To get the rest of the information (the weight that's assigned to the factor, and the score that the manager and up to 9 other people could provide for each individual factor), I need to find:a) all factors that have been assigned to this positionANDb) the scores and weights for each factorIf I have committed some kind of Cartesian blooper, could somebody point out to me where my think-o happens?Thanks much.
So I am still not understanding why you think getting mroe rows back is not normal. You are joining a one side to a many side table.quote:
I need to find:a) all factors that have been assigned to this positionANDb) the scores and weights for each factor
SELECT t1.facid, t2.anotherfield, etc. FROM YourFactorTable INNER JOINYourRelatedTable t2 ON t2.facid = t1.facid
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
Crumpelfungus
Starting Member
5 Posts |
Posted - 2005-06-08 : 12:12:15
|
| Thanks for your replies.I see what I forgot to point out: Every factor that exists in the one table has a corresponding record in the other table.So, if I query TABLE A for all 14 factors (which I must be doing wrong), there WILL BE a corresponding value in TABLE B with, for example, the factor's weight information. (I verified that.)HOWEVER, the "tricky" part is that:- Factors are not unique. Several different clients can have their own version of Factor 17, for example; the only thing that distinguishes "Factor 17 from Company ABC" from "Factor 17 from Company XYZ" would be the client ID.- Additionally, several employees from the SAME company can have the same factor assigned to their positions - but with different weights assigned. Therefore, my predecessor created a table that would basically list all the factors -- and the way to figure out whom it belongs to is to look up the appraisal's ID.- So, I end up with a table that has many, many factors that are essentially the same -- except for that appraisal ID and the weight. - Positions are unique. Every position has its own PK/ID and has a number of factors assigned to it.Look, I know this can be confusing, but I appreciate all of your feedback to this point already. I'll just have to get my eyes off this stuff and come back later again when I can think clearly.Again, thanks for all your replies.dpbPS: By the way, jermiz, I looked at your web site: Great work! |
|
|
|
|
|
|
|
|
JOIN Stumper - Dope slap, please...