(2011-10-27, 12:30 PM)Tom K. Wrote: [ -> ]ok, that works fine, apart from one small thing:
If I search for "catering" (that is keyword ID: 5) it gives me this:
PID: Keyword Matches
2 : 1
3 : 0
4 : 0
5 : 0
6 : 0
etc...
Basically, it lists EVERY distinct PID, even if it has 0 keyword matches. This makes for a MASSIVE overhead. Is there any way to alter that SQL to only include PID's in the resultset that have 1 or more keyword matches?
I would also like to bring in the 'URL' and 'PageTitle' from the table: "search_Pages", and only include any pages in the final results set that have 'Crawled' set to 1. So i'm thinking some kind of INNER JOIN 
Just add "AND NUM > 0" to your query (before the FROM clause) to eliminate the zero issue. As for retrieving from another table, just do a left join using the foreign key.
All the best,
Imad Jomaa.
_a
Would I not want an inner join for this?
If you want results from the left table that don't have matches in the right table to display, then a left (outer) join is what you want. Otherwise, inner join.
All the best,
Imad Jomaa.
e
So it'll be inner then
only results found in both tables should be shown.
Next problem, where would I put the inner join?
I'm a SQL noob
SELECT DISTINCT a.PID AS RPID, (SELECT COUNT(a.KID) FROM your_table AS a WHERE (PID = RPID)* AND a.KID IN ('1', '2', '3', '4', '5')) AS NUM AND NUM > 0 FROM your_table as b INNER JOIN another_table as c ON b.primary_key = c.foreign_key ORDER BY NUM DESC
* Not sure why you have that in there, PID will always equal RPID in your query anyway.
The code above should work, I haven't done joins however while grabbing from two other tables as well, so it might be different, but I just treated it as performing two joins.
All the best,
Imad Jomaa.
_a
(2011-10-28, 10:32 PM)Imad Jomaa Wrote: [ -> ]SELECT DISTINCT a.PID AS RPID, (SELECT COUNT(a.KID) FROM your_table AS a WHERE (PID = RPID)* AND a.KID IN ('1', '2', '3', '4', '5')) AS NUM AND NUM > 0 FROM your_table as b INNER JOIN another_table as c ON b.primary_key = c.foreign_key ORDER BY NUM DESC
* Not sure why you have that in there, PID will always equal RPID in your query anyway.
The code above should work, I haven't done joins however while grabbing from two other tables as well, so it might be different, but I just treated it as performing two joins.
All the best,
Imad Jomaa.
_a
OK, i modified yours a bit to this:
SELECT DISTINCT a.PID AS RPID, (SELECT COUNT(a.KID) FROM search_KeywordMapping AS a WHERE (PID = RPID) AND a.KID IN ('2','3')) AS NUM AND NUM > 0 FROM search_KeywordMapping as b INNER JOIN search_Pages as c ON b.PID = c.PID ORDER BY NUM DESC
And it gives me an error: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND NUM > 0 FROM search_KeywordMapping as b INNER JOIN search_Pages as c ON b.PI' at line 1"
Also, the extra info is only brought from one table (search_Pages), so there should only be one inner join, correct?
Can you PM me your table structures, it'll make it easier for me to write the query up for you instead of guess and check work.
Thanks,
Imad Jomaa.
s
SELECT DISTINCT a.PID, c.URL, c.PageTitle, (SELECT COUNT(b.KID) FROM your_table as b WHERE b.PID = a.PID AND b.KID IN ('1', '2', '3', '4', '5')) AS NUM FROM your_other_table as a INNER JOIN search_Pages as c ON a.primary_key = c.foreign_key WHERE NUM > 0 AND c.Crawled ='1' ORDER BY NUM DESC
I hope you received the PM I sent yesterday. Anyway, the query above should do what you want. I left the first third of the query the way it was before, since I couldn't really understand what tables were being used. Furthermore, you'll need to replace your_table and your_other_table, and include what you want to select, in the query.
All the best,
Imad Jomaa
o
I still get an
#1054 - Unknown column 'NUM' in 'where clause'
error
Using this SQL:
SELECT DISTINCT a.PID, c.URL, c.PageTitle, (SELECT COUNT(b.KID) FROM search_KeywordMapping as b WHERE b.PID = a.PID AND b.KID IN ('1', '2', '3', '4', '5')) AS NUM FROM search_KeywordMapping as a INNER JOIN search_Pages as c ON a.PID = c.PID WHERE NUM > 0 AND c.Crawled ='1' ORDER BY NUM DESC;