Hello all.
I have two tables in a database, one of which ("accesslog") contains a
series of host names (i.e., ao.net) and another ("countrycodes") that
contains the ISO descriptions for various domain suffixes (i.e., an entry
".net" with a description "US Networks").
What I'm trying to do is get a count of the hits from various countries
determined by their suffix.
I'm trying to figure out of there is a way to do this via SQL. I've been
trying something like
SELECT hostname, COUNT(hostname) AS num, descrip FROM accesslog,
countrycodes WHERE RIGHT(hostname, 5) LIKE countrycodes.code GROUP BY
hostname ORDER BY num DESC;
but that doesn't work (comes up with an empty set). At this point I'm sort
of stuck.
Sir, without wildcards, LIKE is equivalent to the equal sign. Try
LIKE Concat('%', countrycodes.code)
Another way is
WHERE Substring_index(hostname, '.', -1) = countrycodes.code
One of the two should work for you.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak