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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments