Need help with mysql query.

Question

So I am trying to do a somewhat complex query and am having trouble. I have structured it like this as it seems to be the most logical:

SELECT (intake.id, s_matters.id, s_calls.cid, s_events.id) AS id, 
      (intake.name, s_matters.casename, s_calls.name, s_events.subject) AS title 
 FROM intake, s_matters, s_calls, s_events 
WHERE title LIKE '%mi%'

That returns:

Error Code : 1241
Operand should contain 1 column(s)

So I know it has something to do with the bracket structure, but I don't know how to do it correctly.

Answer 1

An alias can only refer to one column. If the alias is intended for the last column in each list, do this:

SELECT intake.id, s_matters.id, s_calls.cid, s_events.id AS id, intake.name, 
    s_matters.casename, s_calls.name, s_events.subject AS title 
FROM intake, s_matters, s_calls, s_events 
WHERE title LIKE '%mi%' 

Also, you are missing ON clauses from your joins. What is the query trying to accomplish?

Answer 2

It isn't clear what you are trying to do

SELECT concat(intake.id, s_matters.id, s_calls.cid, s_events.id) AS id, concat(intake.name, s_matters.casename, s_calls.name, s_events.subject) AS title FROM intake, s_matters, s_calls, s_events WHERE title LIKE '%mi%

May be what you intended. And as redfilter points out you your joins are missing ON clauses.

Answer 3

I suspect that what is required would best be returned by the following:

SELECT id, name title, 'intake' table 
FROM intake WHERE name LIKE '%mi%'  
UNION ALL
SELECT id, casename title, 's_matters' table 
FROM s_matters WHERE casename LIKE '%mi%'  
UNION ALL
SELECT cid AS id, name title, 's_calls' table 
FROM s_calls WHERE name LIKE '%mi%'  
UNION ALL
SELECT id, subject title, 's_events' table 
FROM s_events WHERE subject LIKE '%mi%'