[thelist] SQL Loser
Fred Jones
fredthejonester at gmail.com
Tue Aug 12 08:03:16 CDT 2008
I will admit I am not SQL pro. :(
Here is the query I am using to fetch search results. I did not write
the schema and it's not flexible, so don't ask me to change that part.
:)
SELECT field_article_title_value, tid, nid FROM
(
SELECT content_type_book.field_article_title_value, term_node.tid,
node_revisions.nid
FROM
(
(
node_revisions
left join node on node.nid = node_revisions.nid
)
left join content_type_book on content_type_book.nid = node_revisions.nid
)
left join term_node on term_node.nid = node_revisions.nid
WHERE node_revisions.body like '%SEARCHTERM%' and node.type='book'
group by node_revisions.nid order by node_revisions.nid
)
as XXXXX
group by field_article_title_value, tid order by nid
This works and provides precisely the results I want. In order to get
the number of results, however, in order to be able to page the
results, the only solution I have is to wrap that entire query in
"SELECT COUNT(*) from ( ... ) as temp_table" which makes the inner
SELECT now a sub-sub-select. I am given to understand that this is
non-optimal, but I haven't yet come up with a better solution, neither
for the actual SQL nor for the count(*) part.
Any suggestions (if anyone is able to decipher these queries) are appreciated.
The problem seems to be the nested group by clauses. I need the
grouping of course, but I am wondering if they don't have to be quite
so nested.
Thanks
More information about the thelist
mailing list