Help with a join

nightdesigns

I have a logging system setup. The main DB keep track of the time, page, and IP address of the person visiting. The second DB is a DB of IP addresses and who they belong to.

I was able to do the successfully using this

SELECT * FROM `loggingdetailed` LEFT JOIN ip_addresses ON loggingdetailed.IP = ip_addresses.IP ORDER BY `record` DESC LIMIT 0 , 100

Now I want to remove my IP address from the loggingdetailed results. I tried using this:

SELECT * FROM `loggingdetailed` LEFT JOIN ip_addresses ON loggingdetailed.IP = ip_addresses.IP WHERE `loggingdetailed.IP` != CONVERT( _utf8 'x.x.x.x' USING ascii ) COLLATE ascii_general_ci ORDER BY `record` DESC LIMIT 0 , 100

But that is returning: "#1052 - Column 'IP' in where clause is ambiguous "

So I tried where loggingdetailed.IP != .... it resulted in: #1054 - Unknown column 'loggingdetailed.IP' in 'where clause'

What is the proper syntax to do what I want?

bretticus

I'm not sure about the result you are after, but the syntax error is pretty straight forward, in

SELECT * FROM `loggingdetailed` LEFT JOIN ip_addresses ON loggingdetailed.IP = ip_addresses.IP WHERE `loggingdetailed.IP` != CONVERT( _utf8 'x.x.x.x' USING ascii ) COLLATE ascii_general_ci ORDER BY `record` DESC LIMIT 0 , 100

change

`loggingdetailed.IP` != CONVERT(...

`loggingdetailed`.IP != CONVERT(...

loggingdetailed.IP != CONVERT(...

nightdesigns

Thank you, I wasn't aware of the proper use of the "`". Now I know.

bretticus

nightdesigns wrote:
Thank you, I wasn't aware of the proper use of the "`". Now I know.

I use the backtick all the time, even though, technically, it's only needed when quoting a column or table that happens to be a reserved word in MySQL. I think my over-use is due to how phpmyadmin liberally quotes tables and columns (the 'ole copy and paste.) 😃

What I didn't know (until tonight) is that anything after the dot (.) does not need to be quoted (ever.) That's because the dot means there's an identifier in front, so even a column named select can be referenced by

tablename.select

and MySQL doesn't choke on it. So thanks for helping me learn more about them as well.

nightdesigns

So we both learned something new tonight. PhpMyAdmin is an awesome piece of software, it has made my life very easy. But it too occasionally has a limitation (or I have one and haven't figured out how to tell PhpMyadmin to do what I want.

Thanks again.

bretticus

nightdesigns wrote:
PhpMyAdmin is an awesome piece of software...

I totally agree. I prefer it to everything else. Actually,I think, the liberal backtick-ing is a good feature for a web-based MySQL application. In other words, it's very convenient quoting everything so that it never sends a bad query. Yeah, being that it is a Web-based platform does present a few challenges now and again.