Crusader Posted December 20, 2006 Share Posted December 20, 2006 [code]// query 1"SELECT * FROM sectors WHERE (x BETWEEN '%s' AND '%s') AND (y BETWEEN '%s' and '%s') ORDER BY x,y limit 9"// query 2"SELECT sector,owner,ship,mothership,name FROM ships WHERE sector='%s' ORDER BY id"// merged queries"SELECT sectors.*,ships.sector,ships.owner,ships.mothership,ships.name FROM sectors,ships WHERE (sectors.x BETWEEN '%s' AND '%s') AND (sectors.y BETWEEN '%s' AND '%s') AND ships.sector=sectors.id ORDER BY sectors.x,sectors.y"[/code]I merged two of my queries to try and save connections... the problem is I'm not sure on how to make it output all the values from the first query.Right now it just seems to get all of the values in query 2 and doesn't output the rest of query 1. How can I get it to output all of the values from query 1 and output the results in query 2 if any exist.---Important Table Structures---sectors:id,x,y,shipsships:sector,id"id" in the "sectors" table corresponds to "sector" in the "ships" table.Right now I'm running both queries seperately, if a result in query 1 says that "ships" is greater than zero then it runs query 2. Any ideas on how I can do the same with one query?Thanks! Link to comment https://forums.phpfreaks.com/topic/31307-solved-help-with-merged-query-results/ Share on other sites More sharing options...
btherl Posted December 20, 2006 Share Posted December 20, 2006 Can you give your actual output and expected output? I'm having trouble understanding from your description.The query looks logical. It should return a row with ship data from the ships table for every ship in the sectors with x between %s and %s and y between %s and %s. Link to comment https://forums.phpfreaks.com/topic/31307-solved-help-with-merged-query-results/#findComment-144987 Share on other sites More sharing options...
Crusader Posted December 20, 2006 Author Share Posted December 20, 2006 The output for query 1 brings up to 9 results (sector ids, etc).If there's a specific value found in query 1 then it will run query 2 to look for a matching sector id.My merged query only does what query 2 should, it gets results from query 1 but only prints out results that are valid in query 2. Link to comment https://forums.phpfreaks.com/topic/31307-solved-help-with-merged-query-results/#findComment-144997 Share on other sites More sharing options...
btherl Posted December 20, 2006 Share Posted December 20, 2006 Can you paste the actual output from each of the queries?Do you want to keep results from query 1 even when there is no matching ship from query 2? If so, use:[code=php:0]"SELECT sectors.*,ships.sector,ships.owner,ships.mothership,ships.name FROM sectors LEFT JOIN ships ON (ships.sector = sectors.id) WHERE (sectors.x BETWEEN '%s' AND '%s') AND (sectors.y BETWEEN '%s' AND '%s') ORDER BY sectors.x,sectors.y"[/code]That will put nulls for the ships table where there is no matching row. Link to comment https://forums.phpfreaks.com/topic/31307-solved-help-with-merged-query-results/#findComment-145002 Share on other sites More sharing options...
Crusader Posted December 20, 2006 Author Share Posted December 20, 2006 The LEFT JOIN actually did the trick! Thank you so much for such a quick answer! Link to comment https://forums.phpfreaks.com/topic/31307-solved-help-with-merged-query-results/#findComment-145009 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.