CODEWITHSUNDEEP
mysqlsqljoinphpmyadmin
Yahoo
Yahoo

Reputation: 4187

Combining 2 tables- Subquery returns more than 1 row

When i join 2 tables i am getting an error

select * from picture where id=(SELECT id FROM user where age= 40 )

#1242 - Subquery returns more than 1 row

What is the best way to join these 2 large data tables? I want to have all the results

Upvotes: 0

Views: 1236

Answers (4)

beck03076
beck03076

Reputation: 3308

From what Im guessing out of what Im seeing,

You have a "picture" table and a "user" table.

  1. Table names should be plurals. Make it "pictures" and "users".
  2. Define your relationships first. In your case, one user has_many pictures and one picture belongs_to one user.(One picture [on a peaceful world] wont belong to many users).
  3. In your pictures table, have an "user_id" column and populate that column with the appropriate user_ids.
  4. Now,

You want all the pictures of users whose ages are exactly 40.

You can do a,

SELECT *   
FROM pictures 
WHERE user_id IN (SELECT user_id 
                  FROM users 
                  WHERE user_age = 40)

Was that your requirement?. Do I make any sense?

Upvotes: 2

user520288
user520288

Reputation:

In the where clause, when you assign a value to id, SQL expects a single value. The problem is that the second query SELECT id FROM user where age= 40 returns more than one row.

What you can do is

select * 
from picture 
where id in
    (select id from user where age = 40)

or

select *
from picture p, user u
where p.id = u.id
and u.age = 40

Upvotes: 3

bjornruysen
bjornruysen

Reputation: 850

Try using 'IN' instead of '='

select * from picture where id IN (SELECT id FROM user where age= 40 )

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

Replace = by IN

select * from picture where id in (select id from user where age = 40)

Note, this is only "semi-joining" the two tables, i.e. you will only get pictures as results, not a picture-user relation. If you're actually trying to do the latter, then you'll have to inner join the two tables:

select * from picture p
join user u on p.id = u.id -- are you sure about this join condition?
where u.age = 40

Upvotes: 5

Related Questions