I have an sql statement that needs to be executed of a list of
parameters.

say for example I have a table of Clients [LastName, Nationality]

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE %Mc%

So this gives me a list of all people like McNeil, McKnight and their
nationality.

but what if I wanted to do this for another name like Mac or OB or OC

Can I put a list of 'likes' in the SQL statement?

SELECT LastName, Nationality FROM Clients WHERE LastName LIKE (%Mc%,
%Mac%, %OB%, %OC%) ORDER BY LastName

and order them by the Mc, Mac, OB, OC parameter...

What does the syntax look like for this if it can be done?

On 23-02-11 18:48, SpreadTooThin wrote:
SELECT LastName, Nationality, 1 FROM Clients WHERE LastName LIKE %Mc%
UNION
SELECT LastName, Nationality, 2 FROM Clients WHERE LastName LIKE %Mac%
UNION
SELECT LastName, Nationality, 3 FROM Clients WHERE LastName LIKE %OB%
UNION
SELECT LastName, Nationality, 4 FROM Clients WHERE LastName LIKE %OC%
ORDER BY 3, LastName

--
Luuk

On 2011-02-23 18:48, SpreadTooThin wrote:
You could use a temporary table, insert your patterns, and join it
against clients. I'll use a derived table here, but the principle is the
same:

select c.*
from ( select 'Mc%' as pat
union
select 'Ab%' as pat) x
join clients c
on c.lastname like x.pat;

If you want a specific order:

select c.*
from ( select 'Mc%' as pat, 1 as nr
union
select 'Ab%' as pat, 2 as nr) x
join clients c
on c.lastname like x.pat
order by nr;


/Lennart

On Feb 23, 11:20*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
Thanks Luuk.
The 1, 2, 3, 4 that you added to the Select statement are constants
that end up in the record set?
If so, then what is the purpose of the 3 in the 'order by 3,
Lastname'?

On Feb 23, 5:48*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:

On 23-02-11 21:04, SpreadTooThin wrote:
On http://dev.mysql.com/doc/refman/5.0/en/select.html
it says:
SELECT
......
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]


After ORDER BY its possible to use a col_name, an expression, or a
position. This last one is what is used above..


--
Luuk

Sometime people take analogies toooo far...

SpreadTooThin wrote:
SELECT LastName, Nationality FROM Clients WHERE
LastName LIKE '%Mc%' OR
LastName LIKE '%Mac%' OR
LastName LIKE '%OB%' OR
LastName LIKE '%OC%'
ORDER BY LastName ASC



If your programming/scripting language has support for it, you can store
a soundex of the name and search by that.

--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-

On 2/23/2011 7:39 PM, Norman Peelman wrote:
Which does not provide the sort order the op requests.

Luuk's response, while a bit more complicated, is also correct.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Feb 23, 10:35*pm, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote:
I was pointing out that your LIKE arguments would produce names were
those letters appear anywhere in the name, not only where they are at
the beginning as your post seemed to suggest.