rvdveen27 Posted July 25, 2015 Share Posted July 25, 2015 Hello guys, Quick and (I think) simple question. Is it possible to retrieve data twice within the same INNER JOIN? For example, I have a table drive_routers, which holds the 'driverid' & 'handledby'. Now both of these values need to be compared with the "users" table. I was thinking something along the lines of: FROM drive_routes dr INNER JOIN users u ON u.id = dr.driver ,ON u.id = dr.handledby But that does not seem to work. I hope this example gives you kind of an idea what I'm talking about. I googled this too, but could not find an answer if this is possible in some way or not... If it's not possible, what's the best way to go about it? Just use INNER JOIN x2, I assume? Thanks in advance. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted July 25, 2015 Share Posted July 25, 2015 You saying u.id must equal both dr.driver and dr.handleby? INNER JOIN users u ON u.id = dr.driver AND u.id = dr.handledby or u.id can equal either dr.driver or dr.handledby INNER JOIN users u ON u.id = dr.driver OR u.id = dr.handledby Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 25, 2015 Solution Share Posted July 25, 2015 JOIN twice and give them different aliases SELECT u1.name as driver, u2.name as handler FROM drive_routes dr INNER JOIN users u1 ON u1.id = dr.driver INNER JOIN users u2 ON u2.id = dr.handledby 1 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted July 25, 2015 Author Share Posted July 25, 2015 JOIN twice and give them different aliases SELECT u1.name as driver, u2.name as handler FROM drive_routes dr INNER JOIN users u1 ON u1.id = dr.driver INNER JOIN users u2 ON u2.id = dr.handledby This is how I have it done now indeed. It seems like the best solution. Another problem that I'm running in, is that in the database there are already over 300 entries. I recently added the column "handledby", which has set the value of the handledby column for all over 300 entries to "0". Having this in my code: INNER JOIN users hb ON hb.id = dr.handledby makes it so that the queries only gives the results of the entries that don't have "handledby" set to "0". Because we don't have a user with "id" "0", I only get 1 entry back (my test entry), which is the only one with "1" in the "handledby" column. Is there a way to make it so that it still shows the entries with a "0" in handled by, while also showing other values? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 25, 2015 Share Posted July 25, 2015 Use LEFT JOIN instead of INNER JOIN 1 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted July 25, 2015 Author Share Posted July 25, 2015 And another new thing learned. Thanks once again. Works amazing! 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.