MySQL Multiple Select, Multiple Where Clauses

Question

Instead of running hundreds of SELECT queries for one operation, I want to just run one big query, which I'm hoping will ease the load on my server.

SELECT (
(SELECT link_type_id FROM connections WHERE (node_to_id = '0' AND node_from_id = '1') OR (node_from_id = '1' AND node_to_id = '0')),
(SELECT link_type_id FROM connections WHERE (node_to_id = '0' AND node_from_id = '2') OR (node_from_id = '2' AND node_to_id = '0'))
)

There will be many more SELECTS in this query, but even the two aren't working. When I run this code I get the error:

Operand should contain 1 column(s).

Any suggestions? Thanks!

You need to use UNION SELECT and lose the outer SELECT, it won't really be that much more efficient though. — doublesharp
– doublesharp, Commented Apr 9, 2013 at 4:06
what is your criteria for selection? (node_to_id = '0' AND node_from_id = '1') OR (node_from_id = '1' AND node_to_id = '0') doesnt seem to differ.. both options are the same — Ajo Koshy
– Ajo Koshy, Commented Apr 9, 2013 at 4:07
@doublesharp it's less about efficiency, more that I want to reduce the number of time I query the database. — user1114864
– user1114864, Commented Apr 9, 2013 at 7:09
That's what I meant, a UNION is a separate query with it's own execution plan even as part of a larger query. — doublesharp
– doublesharp, Commented Apr 9, 2013 at 15:57

curtisk · Accepted Answer · 2013-07-10 14:55:54Z

7

You can try below may be but you may need UNION

SELECT link_type_id FROM connections 
WHERE (node_to_id = '0' AND node_from_id = '1') 
OR (node_from_id = '1' AND node_to_id = '0')
UNION
SELECT link_type_id FROM connections 
WHERE (node_to_id = '0' AND node_from_id = '2') 
OR (node_from_id = '2' AND node_to_id = '0')

edited Jul 10, 2013 at 14:55

curtisk

20.2k4 gold badges62 silver badges75 bronze badges

answered Apr 9, 2013 at 4:30

shola

7044 silver badges11 bronze badges

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

Comments

Explosion Pills · Accepted Answer · 2013-04-09 04:11:47Z

3

This is caused by the parentheses outside of the columns. Even something as simple as:

SELECT ((SELECT 1), (SELECT 1))

Will yield this error -- the problem is that MySQL can only display one column per ... well ... column, and the entire () wraps a single column. If you remove the outer parentheses it will display each SELECT in a separate column.

answered Apr 9, 2013 at 4:11

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Collectives™ on Stack Overflow

MySQL Multiple Select, Multiple Where Clauses

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related