General Question

richardhenry's avatar

Is there something wrong with my MySQL query?

Asked by richardhenry (12623 points ) July 9th, 2008

This query is refusing to run:

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

Returning:

Unknown column ‘full_name’ in ‘where clause’

Am I missing something? I can order by full_name, but cannot use it in a where clause. I’ve spent a while looking for some documentation on this, but I can’t seem to find anything that covers it.

Any help is greatly appreciated!

Observing members: 0 Composing members: 0

10 Answers

richardhenry's avatar

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.

Breefield's avatar

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.

sferik's avatar

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.

chaosrob's avatar

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 ’ ’.

sferik's avatar

@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.

jasonjackson's avatar

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. :)

richardhenry's avatar

@jasonjackson: Helpful input, thanks. :)

sferik's avatar

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

chaosrob's avatar

@sferik Thanks! Appreciate the boost.

molave's avatar

@jasonjackson, that’s amazing. Thanks, I learned a lot from the explanation. Of course, the other answers shed light, too.

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther