Is there something wrong with my MySQL query?

SELECT CONCAT_WS( ’ ’, first_name, last_name ) AS full_name, username, country, region, CONCAT_WS( ’, ’, region, country ) AS location FROM people HAVING full_name=‘John Doe’

Damn! I’ve been awake for a long time. Silly me.

What exactly are you trying to pull, and what does the datastructure of the table in question look like? I have a feeling there’s an easier way to run that query.

Get rid of the WHERE clause and add:
HAVING full_name = 'John Doe'

You may also need to add: GROUP BY full_name, username, country, region, location
immediately before the HAVING clause.

Shouldn’t you have one more term in your first CONCAT_WS? It looks like you have four in the “AS” immediately after it, but you’re only concatenating three. Maybe you need one more ’ ’.

@chaosrob You’re not reading the SQL properly.

In English, it would read as:
1. cram first_name and last_name into a single field, separated by a space, let’s call it full_name
2. also grab username, country, and region
3. cram region and country into a single field, separated by a comma and a space, let’s call it location

This is known as field aliasing. Field aliases can be referenced in HAVING clauses, but not WHERE clauses, because HAVING conditions are applied after the initial query set is returned.

Your “having” clause works fine, but keep in mind that by doing it that way, you’re asking MySQL to read every row in “people”, evaluating at least one concat_ws() (and it probably evaluates the other immediately too), and then apply the filter once it’s done with that whole operation.

In my experience, that’s the kind of thing that often leads to performance problems under load, due to contention for access to the table (does MySQL still only do table-level locking? I’ve been working with a different RDMS lately..), and heavier use of temp if your people table is at all large.

So anyway, if it’s possible in your situation, you might want to use a where clause like this instead:

where first_name = ‘John’ and last_name = ‘Doe’

That way MySQL can filter first (using an index, if you’ve got one over first_name & last_name), then grab the other column values and evaluate the concat_ws() calls.

If the “John Doe” part is coming from user input – like, you’re allowing users to filter the list of people based on a single input field – and assuming you’re building your SQL in program code, then you could split their input into search terms on any whitespace, trim non-alpha characters from each term, and build a where clause. You’d end up with something like so:

where (first_name = ‘John’ or last_name = ‘John’)
and (first_name = ‘Doe’ or last_name = ‘Doe’)

That changes the semantics of the query subtly, in that a user search for “Doe, John” would produce the same results as one for “John Doe”, but that could be considered a feature. :)

@jasonjackson: Helpful input, thanks. :)

@jasonjackson’s answer is much better than mine. Do what he says.

@sferik Thanks! Appreciate the boost.