CODEWITHSUNDEEP
mysql
mikaeel
mikaeel

Reputation: 55

Getting "subquery returns more than one row" error whenever trying to join a column from another table as alias?

I am stuck with this problem for a whole 2 days. I have a users table and it contains:

+--------+--------+----------+--------------------------------------------+
| userId |  name  | username |          profile_pic                       |
+--------+--------+----------+--------------------------------------------+
|      1 | john   | john123  | http://localhost/profile_pic/user1pic.jpg  | 
|      2 | andrew | andrew   | http://localhost/profile_pi/user2pic.jpg   |
|      3 | doe    | doe      | http://localhost/profile_pic/user3pic.jpg  |
+--------+--------+----------+--------------------------------------------+

I have another table called userpost which contains:

+--------+--------+-------------+----------------------------+
| postId | userId | postMessage |         postImage          |
+--------+--------+-------------+----------------------------+
|      1 |      1 | "Hey"       | http://localhost/post1.jpg |
|      2 |      3 | "Add me"    | http://localhost/post2.jpg |
|      3 |      2 | "boring"    | http://localhost/post3.jpg |
+--------+--------+-------------+----------------------------+

userId is refrenced to users.userId. I am trying to join profile_pic to userpost but mysql is returning error. Here is what I am doing:

 SELECT *, (SELECT profile_pic FROM users 
 INNER JOIN userpost on users.userId = userpost.userId) as profile_pic FROM userpost

But getting Subquery returns more than 1 row error

I know I am doing something stupid with the query. I just want something like this:

 +--------+--------+-------------+----------------------------+--------------------------------------------+    
 | postId | userId | postMessage |         postImage          |    profile_pic                             |
 +--------+--------+-------------+----------------------------+--------------------------------------------+
 |      1 |      1 | "Hey"       | http://localhost/post1.jpg | http://localhost/profile_pic/user1pic.jpg  | 
 |      2 |      3 | "Add me"    | http://localhost/post2.jpg | http://localhost/profile_pic/user3pic.jpg  | 
 |      3 |      2 | "boring"    | http://localhost/post3.jpg | http://localhost/profile_pi/user2pic.jpg   |
 +--------+--------+-------------+----------------------------+--------------------------------------------+

I am having a meeting tomorrow to showcase my prototype app. Help will be appreciated.

Upvotes: 0

Views: 46

Answers (3)

Sankar
Sankar

Reputation: 7107

Try this...

SELECT *, 
 (SELECT top 1 profile_pic FROM users a
 where a.userId = b.userId order by b.postId desc) as profile_pic 
 FROM userpost b

But i am not sure, the above query returns the desired profile picture.

Upvotes: 0

user5992977
user5992977

Reputation:

You are using a sub query not a join. When using subquery in the select, you have to make sure it returns exacly one row like 

SELECT COL1,COL2,(SELECT 1) from YourTable

Or by using a correlated query, which I assume was your purpose but is not required since its from the same table as you select, so just use a simple join:

SELECT s.*, t.profile_pic
FROM users t 
INNER JOIN userpost s on t.userId = s.userId

Upvotes: 2

Rajesh Nadar
Rajesh Nadar

Reputation: 167

You are not using the sub-query correctly.you should make sure that sub-query will return only one row.

Below is the Query that you require.

Select up.postId,u.userId,up.postMessage,up.postImage,u.profile_pic from user u
inner join userpost up on u.userId=up.userId

Upvotes: -1

Related Questions