Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Nov 12, 2019 6:34 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Complicated Database Query Help
PostPosted: Tue Mar 15, 2011 6:59 pm 
Offline

Joined: Thu Oct 12, 2006 7:21 am
Posts: 415
Location: California
Hi Guys. I got a complicated query I need help writing.

I have 'three' tables, here they are listed below.

dim queryString as string = "CREATE TABLE searchphrase ("+_
"id INTEGER NOT NULL,"+_
"phrase VARCHAR(255),"+_
"active TINYINT(1) DEFAULT 0,"+_//Is the keyword currently one of the 'active' one's we are investigating.
"score INTEGER DEFAULT 0,"+_//This is the overall score that this keyword has from the set of 'webpage-searchphrase scores, lower is better
"engine VARCHAR(255) DEFAULT 'google',"+_
"UNIQUE(phrase),"+_
"PRIMARY KEY(id)"+_
");"+_
"CREATE TABLE webpage ("+_
"id INTEGER NOT NULL,"+_
"url VARCHAR(255),"+_
"PRIMARY KEY(id)"+_
"UNIQUE(url)"+_
");"+_
"CREATE TABLE webpage_searchphrase ("+_
"resultindex INTEGER DEFAULT 0,"+_
"cost FLOAT DEFAULT 0,"+_
"score FLOAT DEFAULT 0,"+_
"title VARCHAR(255),"+_
"pagerank INTEGER DEFAULT 1,"+_
"rootpagerank TINYINT(1) DEFAULT 0,"+_ // Boolean - true if pagerank contains a root PageRank
"webpageid INTEGER NOT NULL,"+_
"searchphraseid INTEGER NOT NULL,"+_
"FOREIGN KEY(webpageid) REFERENCES webpage(id),"+_
"FOREIGN KEY(searchphraseid) REFERENCES searchphrase(id),"+_
"PRIMARY KEY(webpageid,searchphraseid)"+_
");"


The query I'm currently writing but not finished with yet is below

SELECT webpage_searchphrase.title,webpage_searchphrase.pagerank,webpage_searchphrase.rootpagerank,webpage_searchphrase.score,webpage_searchphrase.cost,webpage_searchphrase.resultindex,webpage.url FROM webpage_searchphrase, webpage WHERE webpage_searchphrase.searchphraseid = (SELECT id FROM searchphrase WHERE phrase='dog toys')


What I'm trying to do is make a query that returns all the records from webpage_searchphrase & webpage who's searchphraseid matches that in the searchphrase table. To make this more clear I'll give an example of the type of response I'm looking for.

Lets say I'm searching for 'dog toys'. My program basically records the top 10 site data from Google about how the title was written & it's url, it's page rank (scoring numbers), and so forth and stores it into the database. The reason I have the url in it's own table is because every URL is unique, it may show up twice for different search results but that is what the webpage_searchphrase table is for. It stores a reference to the unique 'keyword' & 'webpage' information in one table. For example searching 'dog toys' & 'dog toys are great' will still show the same url somewhere on that page, no need to write it twice somewhere, that would cause redundancy.

So all the technical details aside, this query should look inside the webpage_searchphrase table, and based on the keyword we want the information on return all the url's and information inside the webpage_searchphrase table. The columns I should get back are...

resultindex
cost
score (the one in webpage_searchphrase NOT searchphrase table)
title
pagerank
rootpagerank
url (from webpage table)

If someone can help me do this would be much appreciated, I've been trying this now for three hours and need help =(. I have a feeling I have to use a JOIN statement or UNION, but not sure what to do. The query I have so far will return the results of all rows where the searchphraseid equals that of the 'keyword''s matching id in the searchphrase table. That narrow down the 10 results, but then I need a way to display the url that the webpageid references and not sure how to do that.

_________________
Joe Astrahan
CEO - iTechware Inc.
iTechware - Internet, Technology & Software
Google Adwords Campaign Management
"Rocketing you to the top!"
10900 Los Alamitos Blvd, Suite #210
Los Alamitos, CA, 90720
(949)354-4ADS


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Tue Mar 15, 2011 7:46 pm 
Offline

Joined: Thu Oct 12, 2006 7:21 am
Posts: 415
Location: California
I'm not entirely sure, but after some experimention I ... might have this working. Still testing to see if this is solid or not.

SELECT webpage_searchphrase.title,webpage_searchphrase.webpageid,webpage_searchphrase.pagerank,webpage_searchphrase.rootpagerank,webpage_searchphrase.score,webpage_searchphrase.cost,webpage_searchphrase.resultindex,webpage.url FROM webpage_searchphrase JOIN webpage,searchphrase ON webpage_searchphrase.webpageid=webpage.id AND (webpage_searchphrase.searchphraseid=searchphrase.id AND searchphrase.phrase='dog toys') ORDER BY webpage_searchphrase.resultindex

_________________
Joe Astrahan
CEO - iTechware Inc.
iTechware - Internet, Technology & Software
Google Adwords Campaign Management
"Rocketing you to the top!"
10900 Los Alamitos Blvd, Suite #210
Los Alamitos, CA, 90720
(949)354-4ADS


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Tue Mar 15, 2011 8:15 pm 
Offline

Joined: Thu Oct 12, 2006 7:21 am
Posts: 415
Location: California
The above query seems to be working, but I would still appreciate any opinions on improving the query, I'm sure there is some kind of flaw in my logic when designing this.

_________________
Joe Astrahan
CEO - iTechware Inc.
iTechware - Internet, Technology & Software
Google Adwords Campaign Management
"Rocketing you to the top!"
10900 Los Alamitos Blvd, Suite #210
Los Alamitos, CA, 90720
(949)354-4ADS


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Tue Mar 15, 2011 8:36 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
You seem to be putting your search term into the join section instead of a WHERE clause. That won't do exactly what you want. It only limits the join, not the entire result set. I prefer to use separate join clauses for each table, for clarity.
SELECT ...
FROM webpage_searchphrase
JOIN webpage ON webpage_searchphrase.webpageid = webpage.id
JOIN searchphrase ON webpage_searchphrase.searchphraseid = searchphrase.id
WHERE searchphrase.phrase = 'dog toys'
ORDER BY webpage_searchphrase.resultindex


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Tue Mar 15, 2011 8:50 pm 
Offline

Joined: Tue Oct 06, 2009 2:38 am
Posts: 435
timhare has good advice; you should use JOINs wherever possible as it is much faster.


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Wed Mar 16, 2011 3:01 pm 
Offline

Joined: Thu Oct 12, 2006 7:21 am
Posts: 415
Location: California
Thanks for the advice, I'll try this out tonight and see if I can get some speed improvements. If anything it will probably make more sense then the long query I wrote before. I wasn't aware I could connect JOIN statements one after another like that.

_________________
Joe Astrahan
CEO - iTechware Inc.
iTechware - Internet, Technology & Software
Google Adwords Campaign Management
"Rocketing you to the top!"
10900 Los Alamitos Blvd, Suite #210
Los Alamitos, CA, 90720
(949)354-4ADS


Top
 Profile  
Reply with quote  
 Post subject: Re: Complicated Database Query Help
PostPosted: Wed Mar 16, 2011 3:12 pm 
Offline

Joined: Thu Oct 12, 2006 7:21 am
Posts: 415
Location: California
Just tested it, works great! Thanks Tim!

_________________
Joe Astrahan
CEO - iTechware Inc.
iTechware - Internet, Technology & Software
Google Adwords Campaign Management
"Rocketing you to the top!"
10900 Los Alamitos Blvd, Suite #210
Los Alamitos, CA, 90720
(949)354-4ADS


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group