Michel D Michel D - 3 months ago 10
SQL Question

Getting an Operand should contain 1 column(s) error when merging values of two tables

I'll try to explain it the best I can. My English is not very good.

I want to combine values from two tables. It works fine when I only merge one table, like so:

SELECT `friendid`,`friendname`, (SELECT `islogged` FROM `account_data` WHERE `guildcard` = `friendid` ) FROM `guild_data` WHERE `accountid` = '42000007' ORDER BY `friendid` DESC LIMIT 0, 40


But I need two entries from the "account_data" table. I thought I could do it like this:

SELECT `friendid`,`friendname`, (SELECT `islogged`,`lastonline` FROM `account_data` WHERE `guildcard` = `friendid` ) FROM `guild_data` WHERE `accountid` = '42000007' ORDER BY `friendid` DESC LIMIT 0, 40


But apparently that isn't correct, and it throws me an error, namely:

[Err] 1241 - Operand should contain 1 column(s)


How can I make this work with 2 columns? Both values should be taken from the row with the
guildcard
value found from
friendid
.

Hope I've been clear enough.

Answer

It looks like you need to use a JOIN. Try this:

SELECT g.friendid, g.friendname, a.islogged, a.lastonline
FROM guild_data g
LEFT OUTER JOIN account_data a
ON g.friendid = a.guildcard
WHERE g.accountid = '42000007'
ORDER BY g.friendid DESC LIMIT 0, 40

Using a LEFT OUTER JOIN will ensure that you get results in guild_data that don't have rows in account_data. If you know that you will always have a corresponding row in account_data or if you want to exclude rows in guild_data that don't map to anything in account_data, use INNER JOIN instead.

If this doesn't give you the results you want, please let us know what is wrong with it. Post what you got with this query and what you expected to get.

Comments