CODEWITHSUNDEEP
phpmysqlsql
DJC
DJC

Reputation: 1173

SQL query on 3 tables, one with different column name

I have 3 tables;

projects, campaigns, clients.

I need to return results from all 3 tables where the searchterm matches. However, I need to check against 'name' column in projects and campaigns tables, but 'description' column in the clients table. NB This is an existing client database, I have no authority to change the the column names.

Example: user searches for 'data', I need to SELECT:

name as title FROM projects WHERE name LIKE %data%,

name as title FROM campaigns WHERE name LIKE %data%

AND

description as title FROM clients WHERE description LIKE %data%

I'm struggling to combine the queries. Below is what I have so far, which returns a syntax error. I am also thinking I may be taking the wrong approach.

SELECT
    p.name,
    c.name,
    cl.description AS title
FROM
    projects,
    campaigns,
    clients
WHERE
    p.name LIKE % DATA %
OR c.name LIKE % DATA %
OR cl.description LIKE % DATA %

Upvotes: 1

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

You are looking for union all:

SELECT name as title FROM projects WHERE name LIKE %data%,
UNION ALL
SELECT name as title FROM campaigns WHERE name LIKE %data%
UNION ALL
SELECT description as title FROM clients WHERE description LIKE %data%;

If you want to remove duplicates, then use UNION instead of UNION ALL.

Upvotes: 3

Related Questions