mrt003003 Posted June 7, 2011 Share Posted June 7, 2011 Hi there i was hoping for a little help with joining 2 tables. I have 2 tables: Fleet FleetID FleetName PlanetID Ship ShipID ShipName FleetID What i need to do is to select all the FleetIDs from Fleet Where there are 0 Ships assigned to that Fleet (so no ships have that FleetID, so that Fleet has no ships). At the moment i have it working so that All of the ships and fleets are selected except for Fleets with 0 Ships. So if there are no ships with a particular FleetID then that Fleet doesnt appear..I just need the opposite. SELECT f.FleetID, f.FleetName, s.ShipID FROM Planet p LEFT JOIN Ship s ON (f.FleetID = s.FleetID) WHERE p.PlanetID = '4' AND s.Template = '0' Can you help me please?? Thanks Quote Link to comment Share on other sites More sharing options...
shlumph Posted June 7, 2011 Share Posted June 7, 2011 I believe you could left join Fleet onto Ship; so all the fleets are returned, along with all their ships. Then to find out which fleet has no ships, specify where ShipID is null. Since it seems like there can be multiple ships in a single fleet, you may have to also specify DISTINCT: SELECT DISTINCT f.FleetID, f.FleetName, s.ShipID FROM Fleet f LEFT JOIN Ship s ON (f.FleetID = s.FleetID) WHERE s.ShipID IS NULL Quote Link to comment Share on other sites More sharing options...
mrt003003 Posted June 7, 2011 Author Share Posted June 7, 2011 Thats brilliant thank you May I ask... Using my older join: SELECT f.FleetID, f.FleetName, s.ShipID FROM Planet p LEFT JOIN Ship s ON (f.FleetID = s.FleetID) WHERE p.PlanetID = '4' AND s.Template = '0' That selected all the fleets and ships in those fleets.. but it missed out any fleets that had no ships in them. Is it possible to be able to select all the fleets with no ships in as well??? Thanks Quote Link to comment Share on other sites More sharing options...
shlumph Posted June 8, 2011 Share Posted June 8, 2011 The query in your older join seems a bit incomplete; where is f defined, and where is the join on p? 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.