Hi,
Can you use IF?
SELECT iu.time as time, INET_NTOA(iu.ip) as ip,
concat(usc.scheme,"://",us.server,up.path, if(uq.query=" ","",concat("?",uq.query)))
as url,uc.category as
HTH
Quentin
________________________________
From: J S [mailto:[EMAIL PROTECTED]
Sent: Tue 6/07/2004 8:22 a.m.
To: [EMAIL PROTECTED]
Subject: 2 SELECTS in 1
Hi,
I have the following tables in my database for proxy logs. I have broken
down the URL for each line into the following 4 tables:
url_schemes e.g. http
url_servers e.g. www.google.com
url_paths e.g. /search
url_queries e.g q="searchstring"
To get a list of the websites a user has surfed I run the following SQL:
SELECT iu.time as time, INET_NTOA(iu.ip) as ip,
concat(usc.scheme,"://",us.server,up.path) as url,uc.category as
category,ua.useragent as useragent
FROM internet_usage iu
INNER JOIN url_visit uv
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
INNER JOIN url_categories uc
ON uc.id=uv.url_category_ID
INNER JOIN user_agents ua
ON iu.useragent_ID=ua.ID
WHERE iu.uid="u111111"
ORDER BY iu.time
This works fine but the problem is that some URLs have queries so I need to
do
SELECT iu.time as time, INET_NTOA(iu.ip) as ip
concat(usc.scheme,"://",us.server,up.path) as url
OR
concat(usc.scheme,"://",us.server,up.path,"?",uq.query) as url,
ua.useragent as useragent
FROM ....
WHERE iu.uid="u111111"
AND uq.query<>" ";
Can anyone tell me how to do this please? Or if this doesn't make sense, let
me know and I'll send some more information.
Thanks to the list as always.
js.
_________________________________________________________________
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]
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.
