Join amongst 3 tables?

[galvin]

I stink at joins (just can't grasp them for some reason. Hoping someone can help me with this easy request..

 

Say I have 3 tables (with these columns)...

 

administrators  (adminid, email)

 

pools  (adminid, poolid)

 

results  (resultsid, poolid)

 

I need to run a query that gets me all the email addresses from the 'administrators' table, but only for those who have their corresponding poolid in the 'results' table.

 

NOTE:  Every adminid is in the "pools" table so every adminid has a corresponding poolid.  But not every poolid is in the "results" table.

 

Hope that make sense.

 

Can anyone help? 

 

I was thinking something like but it's not working (saying "pools.adminid" is unknown which doesn't make sense to because there is definitely an 'adminid' field in the pools table)

SELECT email FROM `administrators` WHERE administrators.adminid = pools.adminid AND pools.poolid = results.poolid

[Christian F.]

You don't actually have a JOIN there, just a single select from a single table. Which is why MySQL is complaining about the missing fields. Since you haven't actually JOINed the other table(s), MySQL doesn't know where to look to find the data you're asking for.

Luckily, that's an easy fix.

 

In this case, since all of the admins also have (at least) one corresponding record in the pool table, we'll be using INNER JOIN for that. While the results table, which can be empty, is a prime candidate for LEFT JOIN. Meaning the "right hand" (JOINed) table can have 0 rows returned, at it'll still list the results from the "left hand" table.

The pattern of a JOIN is to first specify the type of JOIN you want to use, then the name of the table. Next you can give it an alias, which is recommended to cut down on the length. Then you'll need to tell MySQL which conditions to use to determine what rows to JOIN, which is done by using ON and an expression like with WHERE.

Basically, something like this:

SELECT a.`email`, p.`poolid` FROM `administrators` AS a
INNER JOIN `pools` AS p ON p.`adminid` = a.`adminid`

You can add more JOINs after one another, to fetch the data from all the necessary tables. Once you've done that, you can add a WHERE clause to give a constraint on the entire result set.

Mind you, that you can add more constraints on the JOIN itself, if you have multiple possible results from a JOINed table, but only want some of them. Like if you have a table containing translations, and you only want from a specific language. Then it'd look something like this:

SELECT c.`name`, t.`description` FROM `cards` AS c
INNER JOIN `card_text` AS t ON t.`card_id` = c.`id` AND t.`lang` = 'en'
WHERE c.`id` = 1;

[galvin]

Thanks for the explanation, it does make more sense to me.  Still stink at them, but that helps me a bit .  I just tried the following...

 

SELECT a.`email` , p.`poolid` 
FROM  `administrators` AS a
INNER JOIN  `pools` AS p ON p.`adminid` = a.`adminid` 

And it returned 4,900 rows (which is essentially every email address, since there are 4900 adminids and they all have an entry in the pools table).

 

So then I edited it to be:

 

SELECT a.`email` , p.`poolid` 
FROM  `administrators` AS a
INNER JOIN  `pools` AS p ON p.`adminid` = a.`adminid` 
LEFT JOIN  `results` AS r ON r.`poolid` = p.`poolid` 

And that returned the exact same number of rows as the previous query.  What am I still doing wrong?

[Christian F.]

You just asked for every row with an e-mail and a pool ID, whether or not they had any results. If you only want those with a result, then you'll need to change the second JOIN into an INNER JOIN. If you have any other criteria, then you'll either have to add them to the relevant ON clause, or add a WHERE clause to the query.

[galvin]

Changing to INNER JOIN did the trick.  Thanks for the quick reply!  After reading your explanation it sounded like I had to use LEFT JOIN since the "right" results table could be empty, but makes sense now why INNER JOIN works.

Thanks again!!!!   

[Christian F.]

You're welcome, glad I could help.