Figured it out! Took a gamble and run the below command!
SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,"://",us.server,up.path)
FROM url_visit uv
INNER JOIN internet_usage iu
ON iu.urlid=uv.urlid
INNER JOIN url_servers us
ON us.id=uv.url_server_ID
INNER JOIN url_paths up
ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
ON usc.id=uv.url_scheme_ID
WHERE iu.uid="u752359";Hi Shawn,
I wondered if you might be able to help me with an SQL query. I want to list all the internet sites I've surfed in my database. Here's a query that matches the url with a urlid:
SELECT concat(usc.scheme,"://",us.server,up.path) FROM url_visit uv INNER JOIN url_servers us ON us.id=uv.url_server_ID INNER JOIN url_paths up ON up.id=uv.url_path_ID INNER JOIN url_queries uq ON uq.id=uv.url_query_ID INNER JOIN url_schemes usc ON usc.id=uv.url_scheme_ID AND uv.urlid=10023;
And that works really quickly.
However I want to run this for each urlid matching my uid in table internet_usage.
mysql> desc internet_usage; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | uid | varchar(10) | YES | MUL | NULL | | | time | datetime | YES | | NULL | | | ip | bigint(20) | YES | | 0 | | | urlid | mediumint(9) | | | 0 | | | size | int(11) | YES | | 0 | | +-------+--------------+------+-----+---------+-------+
So maybe it's something like:
SELECT iu.time, INET_NTOA(iu.ip), concat(usc.scheme,"://",us.server,up.path)
FROM internet_usage us, url_visit uv
WHERE iu.uid="u752352"
INNER JOIN url_servers us
ON us.id=uv.url_server_ID
INNER JOIN url_paths up
ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
ON usc.id=uv.url_scheme_ID
But I haven't tried this because my tables are very big and I didn't want to do the wrong join!
Thanks for any help you can offer.
js.
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
