MyBB Community Forums

Seems like the AS NUM wasn't thrown in the proper location, just throw it before the last closing parenthesis (the one before AS NUM).

m

Still a problem with that AS NUM

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') 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

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 'AS NUM) FROM search_KeywordMapping as a INNER JOIN search_Pages as c ON a.PID = ' at line 1

Thanks for sending the database information. The query below (to the best of my knowledge) should do exactly what you want.

SELECT DISTINCT a.PID, a.KID, c.URL, c.PageTitle FROM search_KeywordMapping as a INNER JOIN search_Pages as c ON a.PID = c.PID WHERE c.Crawled ='1' AND a.KID IN ('1') HAVING COUNT(a.KID) > 0 ORDER BY a.KID DESC

If you're having any trouble let me know.

All the best,
Imad Jomaa.

e

(2011-11-11, 10:46 PM)Imad Jomaa Wrote: [ -> ]Thanks for sending the database information. The query below (to the best of my knowledge) should do exactly what you want.

SELECT DISTINCT a.PID, a.KID, c.URL, c.PageTitle FROM search_KeywordMapping as a INNER JOIN search_Pages as c ON a.PID = c.PID WHERE c.Crawled ='1' AND a.KID IN ('1') HAVING COUNT(a.KID) > 0 ORDER BY a.KID DESC

If you're having any trouble let me know.

All the best,
Imad Jomaa.

e

Thanks Imad, I'll test it in the morning and let you know how it goes