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
