[SOLVED] Join more than one tables?

[Saphod]

Hi,

 

I am relatively new to MySQL and I would greatly appreciate some help.

 

I have three tables:

 

log (id, ip, time, url, referer, user_agent)

blocked_IPs (id, ip)

excluded_IPs (id, ip)

 

What I'd like to do is show all necessary information from log where NEITHER the ip is in table blocked_IPs NOR the ip is in table excluded_IPs.

 

If I had to leave out just the ips that are blocked, I would try:

 

SELECT ip, time, url, referer, user_agent
FROM log
LEFT JOIN blocked_IPs ON log.ip = blocked_IPs.ip
WHERE blocked_IPs.ip is NULL

 

Is this right?

 

And how do I now add the 3rd table excluded_IPs to the statement so that the IPs listed there are also excluded from the selection?

 

Or, in other words, how can I use the join on three tables?

 

Thanks in advance!

[KenDRhyD]

SELECT ip, time, url, referer, user_agent
FROM log A
LEFT OUTER JOIN blocked_IPs B ON A.ip = B.ip
LEFT OUTER JOIN excluded_IPs C ON A.ip = C.ip
WHERE B.ip is NULL
AND    C.ip IS NULL

[luca200]

SELECT ip, time, url, referer, user_agent
FROM log
LEFT JOIN blocked_IPs ON log.ip = blocked_IPs.ip
LEFT JOIN excluded_IPs ON log.ip = excluded_IPs.ip
WHERE blocked_IPs.ip is NULL and excluded_IPs.ip is null

 

Another way could be to keep the two table join using a union for the blocked_ips and excluded_ips tables.

I think this way is more performant anyway, as it avoids using a subquery

 

 

Edit: I was not that speed 

[Saphod]

Wow, that was quick... thanks, guys!

 

I must admit I definetly understand luca200's statement better - sorry, KenDRhyD 

 

What I did not know is that you can just add another left join on the log table. So, this is basically two statements merged into one, right?

 

I have absolutely no idea of the UNION statement...

 

@KenDRhyD:

Why would you use LEFT OUTER JOIN?

[luca200]

I must admit I definetly understand luca200's statement better - sorry, KenDRhyD 

His statement does the same as mine.

LEFT OUTER JOIN is exactly the same thing as LEFT JOIN, and he also gives aliases to tables, that makes the query a little bit more readable (and easier to write)

[Saphod]

LEFT OUTER JOIN is exactly the same thing as LEFT JOIN (...)

 

OK, I am trying to comprehend all the differences between all the JOIN statements.

So, what exactly does LEFT OUTER JOIN mean?

 

It must serve a purpose - it ain't just something that can be left out, right? 

[KenDRhyD]

LEFT OUTER JOIN means that the query should return ALL matching records from the first table (based on any criteria in the WHERE clause) and the requested columns from the second table with NULL values supplied of there is no matching record in the second table.

 

Joins are INNER by default (and so some people leave out the word), and INNER joins cannot be LEFT or RIGHT because they require a match.

 

OUTER joins can be defined as LEFT or RIGHT, which controls which table returns records and which returns NULL values when there is no matching record. Again, some parsers presume OUTER when the LEFT or RIGHT keyword is encountered before JOIN, but that is a bad practice to get into -- long experience with SQL has proven that spelling the main phrase word out is full will save much confusion at some point.

[luca200]

It is, actually.

 

You have two kinds of join: INNER JOIN and OUTER JOIN.

An outer join can be LEFT or  RIGHT, meaning that you can have the first (left) or the second (right) table as "base" for your query. So the full syntax would be LEFT OUTER JOIN and RIGHT OUTER JOIN, but as you can see, the word OUTER is not necessary as an inner join can't be "left" nor "right".

An inner join shows only rows in which first table's key and second table's key are corresponding.

An outer join shows first (or second) table's rows even when they do not have a corresponding key in the other table, so you will have NULL values in the other table's fields in the resultset.

You can have FULL OUTER JOIN, too: it means that you'll get rows from both tables that do not have a corresponding one in the other table. But I'm not sure MySql supports it.

 

Hope that's a little bit clearer to you 

 

 

(edit: slower again....  )

[Saphod]

luca200 & KenDRhyD:

Thanks very much to both of you!

I think your explanations made things much clearer for me now.

I'll keep that in mind when I have the next question. ;-)