0

I get this error when attempting the query below. I have a table called access_groups, one called companies, and a join table called access_group_companies. I'm trying to run a search with AND logic using fields in companies.name and access_groups.name. Any idea what is causing this? And if you might know of a better way to run this search, that would be great.

SELECT ag.id, ag.name, c.name
FROM access_groups ag,
     companies c
         JOIN ag ON ag.id = access_group_companies.access_group_id
WHERE CONCAT(ag.name, c.name) LIKE '%ВТБ%';
jarlh
  • 42,561
  • 8
  • 45
  • 63
Gragh
  • 39
  • 7
  • What is the common column in `access_groups` and `companies`? Do you have `id` in `companies` table as well? – Arun Palanisamy Apr 16 '19 at 07:44
  • Don't mix implicit and explicit joins... Use modern, explicit `JOIN` syntax everywhere. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Apr 16 '19 at 07:46
  • Could you clarify regarding implicit and explicit? I thought I was just doing a generic join here – Gragh Apr 16 '19 at 08:36
  • the last part should be more like "LIKE %Тест ВТБ"...this has issues – Gragh Apr 16 '19 at 09:00

1 Answers1

0

Based on what information you provided and assuming there is join column in companies(id) which can be joined with access_group_companies(company_id).

SELECT ag.id, ag.name, c.name 
FROM access_groups ag 
INNER JOIN access_group_companies agc ON ag.id = agc.access_group_id 
INNER JOIN companies c ON c.id = agc.company_id
WHERE CONCAT(ag.name, c.name) LIKE '%ВТБ%';
Deepak Khillare
  • 129
  • 8
  • This is great, but if I have more than one search term, like 'test 123', it doesn't work – Gragh Apr 16 '19 at 08:49
  • The result should have something that appears in the string produced from both columns – Gragh Apr 16 '19 at 08:50
  • Not sure I follow the condition you want on both the columns `ag.name` and `c.name`. May be you want `ag.name LIKE '%test 123%' OR c.name LIKE '%test 123%' `. – Deepak Khillare Apr 16 '19 at 08:55
  • Yes, there may be multiple words - for ag.name or c.name – Gragh Apr 16 '19 at 09:01
  • You need to split the words on whitespace and for each word add a `LIKE ` condition. `ag.name LIKE '%test%' OR ag.name LIKE '%123%' OR c.name LIKE '%test%' OR c.name LIKE '%123%'`. May be this answer is what you were looking for https://stackoverflow.com/questions/7129355/like-wildcard-with-multiple-fields-and-spaces-in-mysql – Deepak Khillare Apr 16 '19 at 09:15
  • yes, that's down the right road...but can't REGEXP be used here? – Gragh Apr 16 '19 at 09:42