#1241 - Operand should contain 1 column(s) when select from two table

Question

i had this error when i used this query

   SELECT id,title,files,price,auction_start,
   (SELECT * FROM `phi_files` where  id in(d.files) order by id desc limit 1)
   FROM phi_ads d Where cat=1 GROUP BY id DESC Limit 6

I have two table phi_ads table have ads , and phi_files have images files , i try to take one file for each ad

any help

score 0 · Answer 1 · answered Oct 27 '17 at 22:59

0

As I see the id is duplicate in both table try in sub select phi_files.id

SELECT id,title,files,price,auction_start,
   (SELECT * FROM `phi_files` where  phi_files.id in(phi_ads.files) order by phi_files.id desc limit 1)
   FROM phi_ads Where cat=1 GROUP BY id DESC Limit 6

answered Oct 27 '17 at 22:59

ferhado

2,363
2
12
35

How does this help with the error, which is that `SELECT *` is returning more than 1 column. – Barmar Oct 27 '17 at 23:07
i just change * to id , work fine thanks for both – Esmail Yahya Gathuth Oct 27 '17 at 23:08

score 0 · Accepted Answer · answered Oct 27 '17 at 23:11

0

When a subquery is used as an expression, it can only return one value. So the query has to return a single row, and that row has to have a single column.

You solved the multiple row problem by using LIMIT 1. To solve the multiple column problem, change * to the specific column that you need.

I also wonder why you're using WHERE id IN (d.files). Since there's only one value in the list, it should be WHERE id = d.files. Or if d.files is a comma-separated list (as I suspect from the column name), you need to use WHERE FIND_IN_SET(id, d.files). See Query with multiple values in a column.

answered Oct 27 '17 at 23:11

Barmar

741,623
53
500
612

sometime d.files contain (20,21,22,23) as text – Esmail Yahya Gathuth Oct 28 '17 at 00:31
Putting comma-separated lists in columns is poor design, you should normalize the data. – Barmar Oct 28 '17 at 00:31

#1241 - Operand should contain 1 column(s) when select from two table

2 Answers2