Where statement that will use a concat

Question

SELECT zlec_status.nazwa                       AS Status, 
       piorytet.nazwa                          AS Priorytet, 
       Concat(koord.imie, ' ', koord.nazwisko) AS `Koordynator`, 
       Concat(zlec_adresy.town, ' - ', zlec_adresy.street, ' ', 
       zlec_adresy.other) 
                                               AS `adres`, 
       zlec_z_dnia,zlec_id, 
       zlec_nr, 
       zlec_do, 
       zlec_ogran, 
       awizacje, 
       awizacja_na_dzien, 
       termin_zamkniecia, 
       tresc, 
       uwagi 
FROM   zlec 
       INNER JOIN koord 
               ON zlec.koord = koord.id 
       INNER JOIN zlec_adresy 
               ON zlec.zlec_addres = zlec_adresy.id 
       INNER JOIN piorytet 
               ON zlec.priorytet = piorytet.id 
       INNER JOIN zlec_status 
               ON zlec.status_zlecenia = zlec_status.id 
WHERE  `zlec_adresy`.`town` LIKE  '%Sz%' LIMIT 0, 10

The table zlec_adresy is the following:

=============================   
id | street | town | other
=============================

As you see there is a concat for the whole address part

Concat(zlec_adresy.town, ' - ', zlec_adresy.street, ' ',zlec_adresy.other)AS `adres`

Starting off - the user has a input field with the address lookup - so he can write either the town name or a street name or the other condition. In my code there will be only filtered through the WHERE clause of the .'town' LIKE userinput. But how can I make it working => Where ||Concat(zlec_adresy.town, ' - ', zlec_adresy.street, ' ',zlec_adresy.other)ASadres|| LIKE userinput

So basically it would filter the As adres for the where statement. Is that possible to do? Or there is another way to accomplish it.

Answer 1

WHERE 
Concat(zlec_adresy.town, ' - ', zlec_adresy.street, ' ', 
       zlec_adresy.other) like concat('%',@variable,'%')

Answer 2

Another, possibly more clean approach uses OR:

WHERE
  town like concat('%',@userinput,'%')
OR
  street like concat('%',@userinput,'%')
OR 
  other like concat('%',@userinput,'%')

Answer 3

You can use HAVING to solve your issue. Something like : SELECT name, CONCAT(street, ' ', town) AS address FROM zlec WHERE zlec.town like '%abc%' HAVING address like '%xyz%'

Answer 4

The query anatomy looks like this:

• it handles the from, because all the other clauses are dependent on it
• it handles the where, to filter out unneeded rows
• it handles the select, so you will get the columns you want

Note, that I did not mention group by, having or order by, since they are not part of your query, so this anatomy is quite a simplified one. The purpose of this simplification was to achieve clarity.

Now, since the select runs after the where, you cannot use the renames from the select in the where, since when the where is executed, the selection was not yet executed.

So you have to do a work-around: - you can define a temporary new relation by (select ...) mynewrelation and use the column renamings of the newrelation, but it is not advisable for this very situation. However, it is good to know about this option, it might be useful in the future - you can use something like the one suggested by Madhivanan, but that solution is not performant, since you are doing very slow string operations to concatenate those fields. As a result, the code will be clear but slow - Hanno Binder's solution is much better, since it uses the benefit of the or of not evaluating the second operand if the first was true. His code is fast, but you do not want to see it

My suggestion: You should define a stored function to make this calculation. That stored function should be equivalent to the solution suggested by Hanno Binder and you would just call that function in your query. So, your source-code will be clear, easy-to-read, correct and performant.