Yesideez Posted March 23, 2009 Share Posted March 23, 2009 Am I missing something here (I've never used JOIN like that) but shouldn't both tables be linked somehow? I can't see that they are... Quote Link to comment Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi They are linked on Person_ID. If you do a normal JOIN then you get a combined record for rows that match up on BOTH tables on the join conditions. There are OUTER JOIN commands if you want a join where records may not be on one table, but not sure that is what you want here. If it is it makes my brain hurt . For example a simple JOIN between Person_Skill and Person_ID on the Person_ID field would give:- 1 1 Java 5 Dave 2 1 C 4 Dave 3 2 Java 4 Nish 4 2 C# 5 Nish 5 1 Problem_Solving 4 Dave 6 1 Communications 4 Dave 7 1 Team_Work 4 Dave 8 2 Problem_Solving 3 Nish 9 2 Communication 3 Nish 10 2 Problem_Solving 2 Nish As you can see there is no row for Nish with C. I cannot see a simple way to combine them to give you a row for a skill (eg, C) for a person who is not on the Person_Skill table. Normally you would do it with a seperate table listing the skills and then joining the people table and the skills tables together with something like the Person_Skill table. You could possibly do it using by having a select distinct of the skills as a table as an extra table and using an OUTER JOIN, but it would be a bodge. Sorry All the best Keith Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 Try this... SELECT p.*,j.* FROM Person_ID AS p,Job_ID AS j WHERE p.id=j.id AND p.Person_Name='Nish' Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 btw, if there isn't always job data for a person I'd use a LEFT JOIN instead. Quote Link to comment Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 Right Il try those suggestions and report back.... Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 Here's the LEFT JOIN for you... SELECT p.*,j.* FROM Person_ID AS p LEFT JOIN Job_ID AS j ON p.id=j.id WHERE p.Person_Name='Nish' Quote Link to comment Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi Unfortunatly that doesn't work. You have joined 2 tables that do not share any key fields, and the table that could do with an OUTER JOIN is not set up for it (ie, I think he wants get a list of all the people and all the possible skills, yet people and skills are both on the same table). All the best Keith Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 I did ask and I was told this... They are linked on Person_ID. All you need to do is change p.id=j.id to p.Person_ID=j.Person_ID Quote Link to comment Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 Guys thank you for your help...I am now using left join and it all seems to be working fine...but I have ran into another problem... If i wanted to sum the scores and sum the weights...how can i code that in an sql statement? I have just about managed to work out the difference... Is this even possible? Quote Link to comment Share on other sites More sharing options...
Maq Posted March 23, 2009 Share Posted March 23, 2009 You can use the SUM() aggregate function. Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 If you want to SUM fields together I think you'll need to make another query although you could try this... SELECT p.*,j.*,sum(p.Weight) AS sumweight,SUM(p.Score) AS sumscore FROM Person_ID AS p LEFT JOIN Job_ID AS j ON p.Person_ID=j.Person_ID WHERE p.Person_Name='Nish' That's the basic idea - not sure if it'll work but I can't see why not. Quote Link to comment Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 I tried that and got the error: #1054 - Unknown column 'p.Weight' in 'field list Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 Change it to suit whatever your field is called - I guessed as I don't know your table structure. Quote Link to comment Share on other sites More sharing options...
Maq Posted March 23, 2009 Share Posted March 23, 2009 It's because "Weight" only exists in the ID_table, you better check everything else as well. Quote Link to comment Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 I tried this: SELECT Person_ID.Person_Name, Person_Skill.Skill_name, Person_Skill.Score, ID_Table.Weight, (Person_Skill.Score-ID_Table.Weight) AS total,sum(ID_Table.Weight) AS sumweight,SUM(Person_Skill.Score) AS sumscore, Job_ID.Job_Name, ID_Table.Skill_Name FROM Job_ID JOIN ID_Table USING (Job_ID) JOIN Person_Skill USING (Skill_Name) JOIN Person_ID USING (Person_ID) WHERE Person_Skill.Skill_Name='Nish' ORDER BY total DESC And I got a table with NULL across all the columns....Im guessing my sql statement isnt up to scratch...is there a more effecient way of doing this? Quote Link to comment Share on other sites More sharing options...
Yesideez Posted March 23, 2009 Share Posted March 23, 2009 I give up... *sigh* Quote Link to comment Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 lol...I feel the same...sorry, but appreciate your help... Quote Link to comment Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 I did ask and I was told this... They are linked on Person_ID. All you need to do is change p.id=j.id to p.Person_ID=j.Person_ID Unfortunatly not. Take a look at the word document earlier for the structure. Job_Id and Person_Id do not share any columns. Job_Id links to ID_Table, which links to Person_Skill which links to Person_ID. The trouble is that the skills table also contains the people with that skill. I would agree that an outer join is what is required, but it is not that trivial. What it could do with is the skills pulling off into a seperate table from either the job skills requirements or the peoples skills. This would give you a list of all the people, and all the skills with a populated id field if that person has that skill:- SELECT Person_Name, b.Skill_name, c.id FROM test_Person_ID a JOIN (select distinct skill_name from test_person_skill) b LEFT OUTER JOIN test_person_skill c ON a.Person_Id = c.person_Id AND b.skill_name = c.skill_name nishmgopal - not sure what you are trying to do with SUM there. You haven't specified which columns to group the data by for the SUM to work on. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.