[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