CODEWITHSUNDEEP
mysqlsql
user1434156
user1434156

Reputation:

Using LIKE %% in query clause with multiple values from different tables

I have a basic query that fetches values from three tables. The three tables : company, classes_by_company, and person. I have a foreign key in all tables with the name company_id. In the where clause I am using LIKE to compare with a random value the values fetched for any results that may have a match. But I am having no success in doing so. I get an error in the sql line WHERE a.id LIKE %1% OR a.company_id LIKE %3% OR c.count LIKE %1% OR p.count LIKE %1%. How can I compare all the values fetched from these three tables against a random value to see if there is a match? SQLFIDDLE

SELECT a.id, a.company_id, a.status,
       c.count AS classes_per_company,
       p.count AS employees_per_company
FROM company a
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM classes_by_company
           GROUP BY company_id) c
       ON a.company_id = c.company_id
LEFT JOIN (SELECT company_id, COUNT(*) as count
           FROM person
           GROUP BY company_id) p
       ON a.company_id = p.company_id
WHERE a.id LIKE %1% OR a.company_id LIKE %3% OR c.count LIKE %1% OR p.count LIKE %1%

Table Schema:

CREATE TABLE company
    (
     id int auto_increment primary key,
     company_id int,
     status varchar(20)
    );

CREATE TABLE classes_by_company
(
 id int auto_increment primary key,
 company_id int,
 class_name varchar(20)
);

CREATE TABLE person
(
 id int auto_increment primary key,
employee_id int,
 company_id int,
 person_name varchar(20)
);

Upvotes: 0

Views: 3744

Answers (2)

MarkHone
MarkHone

Reputation: 381

id is an integer column; the LIKE operator relies on a text data type e.g. nvarchar.

You could convert the id to a string e.g. WHERE CAST(a.id AS VARCHAR(10)) LIKE '%1%' ...

Please also note the single quotes around the %1%.

Upvotes: 0

Barmar
Barmar

Reputation: 781741

LIKE has to be followed by a string, so it should be:

WHERE a.id LIKE '%1%'
   OR a.company_id LIKE '%3%'
   OR c.count LIKE '%1%'
   OR p.count LIKE '%1%'

Upvotes: 2

Related Questions