mysql, use two select

Question

hi, why i get error: Subquery returns more than 1 row

 SELECT name, cat_id,
    (
    SELECT topic
    FROM category
    WHERE cat = u.cat_id
    ) AS topics
    FROM name u

Thanks

Answer 1

because you have two rows with the same cat (I assume it means category)

Answer 2

The sub-query:

SELECT topic
FROM category
WHERE cat = u.cat_id

Is returning more than one result - which you are trying to fit it into a single row.

Answer 3

You have more topics of that category ID.

Answer 4

well, yes. It returns multiple rows in your case. What do you want to have?

You can get 1 row if you add LIMIT 1 at the end of the subquery. You can use JOIN if you want one additional row for each additional topic.

Answer 5

Because you are using a subquery as a column, so it must return a single row. You could add LIMIT 0,1

Answer 6

Hello,

I have maybe a silly answer but why arent't you using a JOIN ?

SELECT name.cat_id, name.name, category.topic
FROM name INNER JOIN category 
ON category.cat = name.cat_id

Answer 7

Because in your sub query you get multiple rows of topic returned.

And in select part sub query such case is not allowed.

Use JOINS if you expect multiple topic rows for user for category.

Answer 8

you need to join the 2 tables

SELECT name.name, name.cat_id, category.topic
FROM name, category
WHERE name.cat_id = category.cat

Answer 9

If your nested select query returns more than one, you can fetch only the first result by saying SELECT TOP 1 topic from category...