Double INNER JOIN?

[rvdveen27]

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.

[Ch0cu3r]

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

[Barand]

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

[rvdveen27]

 

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? 

[Barand]

Use LEFT JOIN instead of INNER JOIN

[rvdveen27]

And another new thing learned. Thanks once again. Works amazing!