Thanks Rich - that's great. Before I found this group, I thought I had
a reasonable understanding of SQL (well, MySQL's implementation of it,
at least) but the truth is there are great chunks that I have no
knowledge of. I've read three or four books about MySQL and PHP all of
which gave (in retrospect) very basic examples of the use of SQL, and
focussed on features such as MySQL's date and time handling functions
that are well covered in PHP, so I came to the conclusion that
post-query processing with PHP was the way to go. I'm wishing now that
I had just sat and read the MySQL manual!
I'm constructing a query that searches through a list of people. Each
person has a firstname, lastname and businessname. Ordering is by
businessname, unless there is no record for businessname, in which case
the entry is ordered by lastname. Rich Ryan and Bill Karwin explain how
to do this in the following post
http://groups.google.co.uk/group/mai...dbea1fcccb53d0)
I'm also using the solution that Rich proposes in the previous post in
this thread to move 'The ' at the start of a business name to the end
of the name
and adding additional search clauses, for example, ordering by results
that start with a certain letter (the SQL below looks for results
starting with 't').
I initially thought that I would be able to use the first query below,
but MySQL responds that it does not recognise 'b' in the WHERE clause,
so I'm having to repeat the 'anti-The' CASE in every WHERE and ORDER BY
clause. Is there any way I can change this to avoid having to repeat
the CASE phrase?
If I use 'b' (as in the first SQL example below) in a simple ORDER BY
clause it works fine - the minute I use 'b' within a clause such as
COALESCE(
IF ( b = '', NULL , b ), CONCAT( ppl.ppl_lastname, ppl.ppl_firstname )
)
it no longer recognises 'b'.
Michael
Table: aa_people
Fields: ppl_id, ppl_salutation, ppl_firstname, ppl_lastname, ppl_dept,
ppl_email, ppl_businessname
----------------------------------------------------------------------------------------------------------
SQL query1:
SELECT ppl.ppl_id, ppl.ppl_salutation, ppl.ppl_firstname,
ppl.ppl_lastname, ppl.ppl_dept, ppl.ppl_email,
CASE WHEN SUBSTRING( ppl.ppl_businessname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_businessname, 5, LENGTH(
ppl.ppl_businessname ) -4 ) , ", The" )
ELSE ppl.ppl_businessname
END
AS b
FROM aa_people ppl
WHERE 1
AND (
( b LIKE "t%" )
OR ( (b = "") && ( ppl.ppl_lastname LIKE "t%" ) )
)
ORDER BY
COALESCE(
IF ( b = '', NULL , b ), CONCAT( ppl.ppl_lastname, ppl.ppl_firstname )
)
----------------------------------------------------------------------------------------------------------
SQL query2:
SELECT ppl.ppl_id, ppl.ppl_salutation, ppl.ppl_firstname,
ppl.ppl_lastname, ppl.ppl_dept, ppl.ppl_email,
CASE WHEN SUBSTRING( ppl.ppl_businessname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_businessname, 5, LENGTH(
ppl.ppl_businessname ) -4 ) , ", The" )
ELSE ppl.ppl_businessname
END
AS ppl_businessname
FROM aa_people ppl
WHERE 1
AND (
(
CASE WHEN SUBSTRING( ppl.ppl_businessname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_businessname, 5, LENGTH(
ppl.ppl_businessname ) -4 ) , ", The" )
ELSE ppl.ppl_businessname
END
LIKE "t%"
)
OR
( (ppl_businessname = "") && ( ppl.ppl_lastname LIKE "t%" ) )
)
ORDER BY
COALESCE(
IF (
ppl_businessname = '', NULL ,
CASE WHEN SUBSTRING( ppl.ppl_businessname, 1, 4 ) = "The "
THEN CONCAT( SUBSTRING( ppl.ppl_businessname, 5, LENGTH(
ppl.ppl_businessname ) -4 ) , ", The" )
ELSE ppl.ppl_businessname
END
),
CONCAT( ppl.ppl_lastname, ppl.ppl_firstname )
)