StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>WHERE</code> does not work on calculated columns, therefore your query, per se, <strong>will not work</strong>.</p> <p>It probably works because you locally have a <code>@concat</code> variable declared. It will also <strong>appear</strong> to work if you run the query twice in specific conditions, because your query might actually assign a <code>@concat</code> variable.</p> <p>What you want is</p> <pre><code>SELECT id as tc_id, firstname, surname, position, CONCAT(firstname,' ',surname) AS concat FROM tc HAVING concat LIKE '%<YOUR SEARCH TERM>%'; </code></pre> <p>As test:</p> <pre><code>-- Declare a minimum table to match the query CREATE TABLE tc (id integer, firstname varchar(20), surname varchar(20), position integer); INSERT INTO tc (firstname, surname) VALUES ('alfa', 'bravo'); -- Your query... SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,' ',surname) FROM tc WHERE (@concat LIKE '%alfa%'); -- ...returns nothing Empty set (0.00 sec) -- The proper query works. SELECT id as tc_id, firstname, surname, position, CONCAT(firstname,' ',surname) AS concat FROM tc HAVING concat LIKE '%alfa%'; +-------+-----------+---------+----------+------------+ | tc_id | firstname | surname | position | concat | +-------+-----------+---------+----------+------------+ | NULL | alfa | bravo | NULL | alfa bravo | +-------+-----------+---------+----------+------------+ 1 row in set (0.00 sec) -- But if I declare a @concat variable SELECT @concat := 'alfa'; +-------------------+ | @concat := 'alfa' | +-------------------+ | alfa | +-------------------+ 1 row in set (0.00 sec) -- Then your query SEEMS to work. mysql> SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,' ',surname) FROM tc WHERE (@concat LIKE '%alfa%'); +-------+-----------+---------+----------+------------------------------------------+ | tc_id | firstname | surname | position | @concat := CONCAT(firstname,' ',surname) | +-------+-----------+---------+----------+------------------------------------------+ | NULL | alfa | bravo | NULL | alfa bravo | +-------+-----------+---------+----------+------------------------------------------+ 1 row in set (0.00 sec) -- "SEEMS" because the select query isn't actually working: UPDATE tc SET firstname = 'delta'; Query OK, 1 row affected (0.28 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Having renamed the only row to "delta", a search for "alpha" should fail, -- but since @concat still holds 'alpha', then the query matches ALL rows: mysql> SELECT id as tc_id, firstname, surname, position, @concat := CONCAT(firstname,' ',surname) FROM tc WHERE (@concat LIKE '%alfa%'); +-------+-----------+---------+----------+------------------------------------------+ | tc_id | firstname | surname | position | @concat := CONCAT(firstname,' ',surname) | +-------+-----------+---------+----------+------------------------------------------+ | NULL | delta | bravo | NULL | delta bravo | +-------+-----------+---------+----------+------------------------------------------+ 1 row in set (0.00 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload