Mysql sub query multiple rows returned

Question

I've gone through many links already but none could solve my problem. So please don't say that it shows less effort though I've worked on MySql before but never came across this situation where I need to use sub-query. So, here's my doubt..

"SELECT * FROM klms as Klm WHERE gcil_id = (SELECT id FROM gcils WHERE genre = 'Clothes')"

Obviously I've more than >1 rows where genre = 'Clothes' so it'll return >1 rows.. but then it shows this error

Error: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

btw I'm using cakephp for my app & I find it easier to use SQL queries.

sgeddes · Accepted Answer · 2013-02-19 19:25:10Z

2

Use IN instaead of equal:

SELECT * 
FROM klms as Klm 
WHERE gcil_id IN (SELECT id FROM gcils WHERE genre = 'Clothes')

Or you can use an INNER JOIN:

SELECT DISTINCT klm.* 
FROM klms as Klm
   INNER JOIN gcils g ON Klm.gcil_id = gcils.id
WHERE g.genre = 'Clothes'

answered Feb 19, 2013 at 19:25

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

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

5 Comments

Colton Over a year ago

I tend to like the first one better. Selecting DISTINCT tends to cause issues with code re-usability (in my experience) as people will copy-paste a query without actually knowing what it does.

sgeddes Over a year ago

@Sparksis -- just wanted to include both options for OP -- DISTINCT might not be necessary depending on data in gcils table. Thanks!

user1984849 Over a year ago

Which one will be more efficient?

user1984849 Over a year ago

I'm fine with both of them but because my data is gonna be huge, I prefer efficient over easier.

sgeddes Over a year ago

@user1984849 -- np -- glad I could help. The short answer to your question, it depends :) IN would probably be more efficient than DISTINCT -- but you may not need DISTINCT -- depends on your data. Run each and see which has a better performance. You can use EXPLAIN to see the execution plan. Hope this helps. Best of luck!

Collectives™ on Stack Overflow

Mysql sub query multiple rows returned

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related