If I understand you correctly then:
<CFQUERY NAME="list_member_dns" DATASOURCE="details_at_dns">
SELECT
dns.dns_id,
dns.dns_domain,
dns.dns_subfolder,
dns.dns_redirect_url
(Select
Sum(count_field) As dnshits
From DNS_hits
Where tableid = dns.dns_id ) AS Totalhits
FROM dns
INNER JOIN
dns_owners on dns.dns_id = dns_owners.dns_id
WHERE dns_owners.dns_member_id = <CFQUERYparam value="#myid#">
</CFQUERY>
Dennis,
Webmaster - www.halomaps.org
.================================================.
|Halo Maps Website - http://www.halomaps.org |
|Halo Movies Website - http://www.halomovies.org |
|Halo CE Chronicles - http://hcec.halomaps.org |
.================================================.
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of
Jason Allen
Sent: Wednesday, July 21, 2010 3:28 PM
To: Open BlueDragon
Subject: [OpenBD] SQL Question (using openbd).. maybe you guys can help
Hi Guys,
I'm using OpenBD/tomcat on Windows 2003 and SQL Server 2008 on Windows
2003.
I have a query that runs that selects all matching records according
to a user_id. Each record is for a shortened url, something like
tinyurl.com. It's fetching all records that were created by a certain
user. (according to their user_id).
I have another table that collects data about each time a url is
visited. (time, date, location).
Then there is a 3rd table which is for listing which member actually
owns each record.
I know DNS is for domain name system, but it's the shorter than
'redirect' and works for me. So don't mind the label.
tables
dns (table for all redirect entries)
dns_hits (table that counts hits on a particular redirect)
dns_owners (table that relates redirects to members)
What I'm trying to do is query select all records from table 'dns'
that match a particular member id. I have a query for that below, and
it works. However, I'm also wanting to compile a hit count for each
redirect that matches in the query.
So as an example, the query below might grab 3 records for a
particular member. This means they have 3 entries in our database for
url shorteners (like having 3 tinyurl.com addresses). The query
fetches the shortened url (tinyurl.com/12kdf) and the url it forwards
to (mylongurl.com/2343/blalal), and it also grabs the unique ID of
each record
Record # - Shortened URL - Destination - unique ID
1 - tinyurl.com/sdfdfd - www.mytesturl.com - 456
2 - tinyurl.com/sdadsfdd - www.mytesturl123.com - 458
3 - tinyurl.com/DFDdfd - www.mytesturl456.com - 455
So the query grabs those records and each record contains a unique ID
from another table. (as seen above). How can I join that table of
hits, use the unique ID to query the table of hits, count the matches,
and include that in the same query? Or will I have to do two queries
and combine the data somehow?
<CFQUERY NAME="list_member_dns" DATASOURCE="details_at_dns">
SELECT
dns.dns_id,
dns.dns_domain,
dns.dns_subfolder,
dns.dns_redirect_url
FROM dns
INNER JOIN
dns_owners on dns.dns_id = dns_owners.dns_id
WHERE dns_owners.dns_member_id = <CFQUERYparam value="#myid#">
</CFQUERY>
I'd want to modify the query above to also join to the dns_hits table
and do a count on the number of records that match the unique_id
(dns.dns_id).
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
online manual: http://www.openbluedragon.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
--
Open BlueDragon Public Mailing List
http://www.openbluedragon.org/ http://twitter.com/OpenBlueDragon
online manual: http://www.openbluedragon.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
