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.

Is there a title column in all of the four tables? If not, which table is it from? But your query is also a cartesian product--you need join criteria — OMG Ponies
– OMG Ponies, Commented Sep 13, 2010 at 19:46
reply from below: I am trying to join all the IDs from the different tables into one column titled "id" and join all the names in another column titled "title", in which the results would have "mi" somewhere in the "title" column. This is really just the first step, I also need a column that has the table name the row came from, so I know what type of data is in each row. — Evan4623
– Evan4623, Commented Sep 13, 2010 at 20:32

D'Arcy Rittich · Accepted Answer · 2010-09-13 19:37:33Z

4

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?

answered Sep 13, 2010 at 19:37

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jaydee · Accepted Answer · 2010-09-13 19:41:12Z

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.

answered Sep 13, 2010 at 19:41

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

2 Comments

Evan4623 Over a year ago

I am trying to join all the IDs from the different tables into one column titled "id" and join all the names in another column titled "title", in which the results would have "mi" somewhere in the "title" column.

Jaydee Over a year ago

Try the concat() function, it joins strings into one string which can then be referenced as a single field. You will need to specify how the tables should be joined though. Eg Where intake.id=s_matters.id etc.

user359040 · Accepted Answer · 2010-09-14 14:16:09Z

0

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%'

answered Sep 14, 2010 at 14:16

user359040

2 Comments

Evan4623 Over a year ago

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table FROM intake WHERE name LIKE '%mi%' UNION ALL SELECT id, casename tit' at line 1

Evan4623 Over a year ago

Actually this was perfect, it just didn't like the virtual column being named "table"... changed to "type" and it worked fine. Thanks!

Collectives™ on Stack Overflow

Need help with mysql query

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related