[snip]
SELECT posterdata.*, IF (aptitle LIKE 'The %',SUBSTRING(aptitle,5),IF
(aptitle LIKE 'A %',SUBSTRING(aptitle,3),IF (aptitle LIKE 'An
%',SUBSTRING(aptitle,4),aptitle))) AS sort_title FROM posterdata WHERE
ap_type='Original Movie Poster' AND sort_title LIKE 'G%' ORDER BY sort_title
ASC
As per previous suggestions, I use sort_title so that film titles like "The
Godfather" get sorted as "Godfather". In the above select I was trying to
narrow the search down so I could grab all the films that started with 'G'
but still using the sort_title.
Since aliases aren't allowed in WHERE's, what can I do?
[/snip]
Use HAVING i.e.
SELECT posterdata.*,
IF (aptitle LIKE 'The %',SUBSTRING(aptitle,5),
IF (aptitle LIKE 'A %',SUBSTRING(aptitle,3),
IF (aptitle LIKE 'An%',SUBSTRING(aptitle,4),aptitle))) AS sort_title
FROM posterdata
WHERE ap_type='Original Movie Poster'
ORDER BY sort_title ASC
HAVING sort_title LIKE 'G%'
ORDER BY sort_title ASC
You may have to swap the HAVING and ORDER BY lines of the query, as I don't
remember right off of the bat that order. I know a GROUP BY has to be before
the HAVING. You can also use multiple HAVING clauses like a WHERE clause
with each additional condition prefaced by AND or OR.
HTH!
Jay
mysql, sql, query
"It's hip to snip!"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
