nishmgopal Posted March 22, 2009 Share Posted March 22, 2009 Hi, This is my code: $sql= "SELECT Job_ID.Job_name, ID_Table.Skill_Name, ID_Table.Weight FROM Job_ID JOIN ID_Table USING (Job_ID) WHERE Job_ID.Job_Name = 'Manager' "; $sql2="SELECT Person_ID.Person_Name, Person_Skill.Skill_Name, Person_Skill.Score FROM Person_ID JOIN Person_Skill USING (Person_ID) WHERE Person_ID.Person_Name='Nish'"; $result1 = mysql_query($sql,$sql2) or die ("Couldn't execute query."); while ($row1=mysql_fetch_array($result1)) { $tblRows .= "<tr>"; $tblRows .= "<td>{$row['Skill_Name']}</td>"; $tblRows .= "<td>{$row['Weight']}</td>"; $tblRows1 .= "<td>{$row1['Score']}</td>"; $tblRows1 .= "</tr>\n"; } I get an invalid arguement error...is what I am trying to do even possible? Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/ Share on other sites More sharing options...
sKunKbad Posted March 22, 2009 Share Posted March 22, 2009 I think what you might want is a sql UNION. Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791113 Share on other sites More sharing options...
Maq Posted March 22, 2009 Share Posted March 22, 2009 I get an invalid arguement error...is what I am trying to do even possible? Yes it's possible but you're doing it the wrong way. Look at the parameters for mysql_query(). You're giving it two strings (queries) when it only takes one. You would either have to use mysql_query() twice, or combine these queries. Can you explain what exactly you're trying to do, specifically what you're trying to extract from the database? Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791118 Share on other sites More sharing options...
nishmgopal Posted March 22, 2009 Author Share Posted March 22, 2009 Basically I am trying to get the Job Name, Person Name, Skill Name Weight and Score.... $sql= "SELECT Job_ID.Job_name, ID_Table.Skill_Name, ID_Table.Weight FROM Job_ID JOIN ID_Table USING (Job_ID) WHERE Job_ID.Job_Name = 'Manager' UNION ALL SELECT Person_ID.Person_Name, Person_Skill.Skill_Name, Person_Skill.Score FROM Person_ID JOIN Person_Skill USING (Person_ID) WHERE Person_ID.Person_Name='Nish'"; $result1 = mysql_query($sql) or die ("Couldn't execute query."); while ($row=mysql_fetch_array($result1)) { } But Now I am having trouble gettin the information I want displayyed in a table.... Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791177 Share on other sites More sharing options...
Maq Posted March 22, 2009 Share Posted March 22, 2009 But Now I am having trouble gettin the information I want displayyed in a table.... So did you get the query working? For a table you need to do something like: </pre> <table> { ?> </ Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791182 Share on other sites More sharing options...
nishmgopal Posted March 22, 2009 Author Share Posted March 22, 2009 Hey thanks for that realy helped...just need to work on my tbl structure i think. Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791207 Share on other sites More sharing options...
Maq Posted March 22, 2009 Share Posted March 22, 2009 Hey thanks for that realy helped...just need to work on my tbl structure i think. Do you mind posting your structure? We can give you feedback for your normalization level and how you can improve the structure. Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791219 Share on other sites More sharing options...
nishmgopal Posted March 22, 2009 Author Share Posted March 22, 2009 Hi I have posted my structure on http://www.phpfreaks.com/forums/index.php/topic,244336.0.html thank you for your help...appreciate it loads. Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791223 Share on other sites More sharing options...
nishmgopal Posted March 22, 2009 Author Share Posted March 22, 2009 My table structure is attached to this post... I have adjusted my sql to look like this: $sql= "SELECT Person_ID.Person_Name, Person_Skill.Skill_name, (Person_Skill.Score-ID_Table.Weight) AS total, 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) ORDER BY total DESC"; But im not sure if this query is correct for the information I want, which is: Job Name, Person Name, Skill Name, Score and Weight....I want to only display the records where the skill name in person and skill name in job match. And then finally I want to calculate the different between score and weight...this is achieved in my sql query.... [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791299 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 can anyone please shed some light on this for me... Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791793 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi Looks about right, although you are not bringing back the score and weight. You need to add those to the SELECT statement. Are you getting an error? All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791799 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 no i am not getting an error, but I dnt know how to use the weight and the score in the select statement... Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791807 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi The select would just be:- $sql= "SELECT Person_ID.Person_Name, Person_Skill.Skill_name, Person_Skill.Score, ID_Table.Weight, (Person_Skill.Score-ID_Table.Weight) AS total, 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) ORDER BY total DESC"; All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791812 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 thank you that worked great, but I am now trying to add a WHERE clause, like this: SELECT Person_ID.Person_Name, Person_Skill.Skill_name, Person_Skill.Score, ID_Table.Weight, (Person_Skill.Score-ID_Table.Weight) AS total, 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 ID_Table.Skill_Name=Person_Skill.Skill_Name ORDER BY total DESC But I get the same output as a statement without the WHERE clause...am I using it correctly and in the right place? Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791832 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi You where clause is doing exactly the same as the lines JOIN Person_Skill USING (Skill_Name). Doing where clauses like that is the old way of specifying the columns on a table join. Do you need to narrow the select down to certain skills? All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791838 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 Basically I want to display all the skills required by the job (ID_Table.Skill_Name) and all the skills a person has (Person_Skill.Skill_name). At the moment for some reason not all the skills are coming up. For example, The Manager has: C, C# and Java and Bob has C, C# and Java, but only C# and Java are showing up. I have attached and image of my result: Person_Name Skill_name Score Skill_Name Weight total Job_Name ------------------------------------------------------------------------------------ Nish Java 4 Java 2 2 Manager Nish C# 5 C# 5 0 Manager C Should also appear but doesnt... Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791861 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi I am afraid I can't see why it shouldn't work. The JOIN will only do so when there are matching records on ALL tables. So if (for example) Bob related back to a Job_ID that didn't exist on the JobID table then no row would appear for Bob. Can you export the data so I can have a play? All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791878 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 Which format would you like me to export it in? Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791888 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi Are you using phpmyadmin? If so export as SQL which makes it easy to make a few test tables. All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791896 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 It wont let me attach .sql files, so I have attached the .doc file Thank you [attachment deleted by admin] Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791903 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi Can't see anything wrong based on that. On the subset of data there I get:- Person_Name Skill_name Score Weight total Job_Name Skill_Name Dave Java 5 2 3 Manager Java Nish Java 4 2 2 Manager Java Dave Problem_Solving 4 4 0 Analyst Problem_Solving Dave C 4 4 0 Manager C Nish C# 5 5 0 Manager C# Nish Problem_Solving 3 4 -1 Analyst Problem_Solving Nish Problem_Solving 2 4 -2 Analyst Problem_Solving All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791930 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 Must just be me...can I just ask which query you ran to get that result? Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791958 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi This one (note I put test_ in front of the table names) SELECT test_Person_ID.Person_Name, test_Person_Skill.Skill_name, test_Person_Skill.Score, test_ID_Table.Weight, ( test_Person_Skill.Score - test_ID_Table.Weight ) AS total, test_Job_ID.Job_Name, test_ID_Table.Skill_Name FROM test_Job_ID JOIN test_ID_Table USING ( Job_ID ) JOIN test_Person_Skill USING ( Skill_Name ) JOIN test_Person_ID USING ( Person_ID ) ORDER BY total DESC LIMIT 0 , 30 All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791959 Share on other sites More sharing options...
nishmgopal Posted March 23, 2009 Author Share Posted March 23, 2009 OK, but can you explain to me why, if I use this code: SELECT Person_ID.Person_Name, Person_Skill.Skill_name, Person_Skill.Score, ID_Table.Weight, (Person_Skill.Score-ID_Table.Weight) AS total, 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_ID.Person_Name='Nish' ORDER BY total DESC Not all the Manager skills appear...C# and Java appear but not C...? This has got me baffeled Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791970 Share on other sites More sharing options...
kickstart Posted March 23, 2009 Share Posted March 23, 2009 Hi On the Person_Skill table there is only one record for C and that is for the Person_ID of 1 which referring to the Person_ID table is Dave. All the best Keith Link to comment https://forums.phpfreaks.com/topic/150605-is-it-possible-to-combine-two-sql-queries/#findComment-791985 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.