Menu
I want to select portfolio of user with the same query that i used for select users.
Here the example that i want.
User Table
----------------------------------------------------------------------
UID NAME USERNAME EMAIL PASSWORD STATUS
----------------------------------------------------------------------
1 Manoj manoj [email protected] ******** 1
2 Test U testing [email protected] ******** 1
3 Company user [email protected] ******** 1
4 Agency company [email protected] ******** 1
User table in my database
Portfolio Table
-----------------------------------
PID UID TITLE STATUS
-----------------------------------
1 1 title 1 1
2 1 title 2 1
3 1 title 3 1
4 2 title 1 1
Portfolio Table in my database
Result I want
----------------------------------
UID USERNAME PORTFOLIO
----------------------------------
1 manoj JSON OBJECT OF PID (1,2,3)
2 testing JOSN OBJECT OF PID (4)
3 user NULL
4 company NULL
Currently I'm trying to use
SELECT u.uid,u.username,(SELECT * FROM portfolio p WHERE u.UID=p.UID) as portfolio FROM users u
Is there any solution for this problem?
518
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).
Answer: B. Multiple-row subqueries return more than one row of results.
The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery.
A bit convoluted, but you could create JSON objects for each row, concatenate them using GROUP_CONCAT and cast the result (wrapped in [] to make it an array) to JSON;
SELECT u.uid, u.username,
CASE WHEN p.uid IS NULL
THEN NULL
ELSE CAST(CONCAT('[',
GROUP_CONCAT(JSON_OBJECT('pid', p.pid,
'title', p.title,
'status', p.status)),
']') AS JSON) END portfolios
FROM user u
LEFT JOIN portfolio p
ON u.uid=p.uid
WHERE p.status = 1
GROUP BY u.uid, u.username;
...which gives...
+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------+
| 1 | manoj | [{"pid": 1, "title": "title 1", "status": 1}, {"pid": 2, "title": "title 2", "status": 1}, {"pid": 3, "title": "title 3", "status": 1}] |
| 2 | testing | [{"pid": 4, "title": "title 1", "status": 1}] |
| 3 | user | NULL |
| 4 | company | NULL |
+------+----------+-----------------------------------------------------------------------------------------------------------------------------------------+
If you're using an older MySQL without JSON support, you could build it as a string;
SELECT u.uid, u.username,
CASE WHEN p.uid IS NULL
THEN NULL
ELSE CONCAT('[',
GROUP_CONCAT(CONCAT('{ "pid":',p.pid,',"title":"', REPLACE(p.title, '"', '\\"'),
'","status":',p.status, ' }')), ']') END portfolios
FROM user u
LEFT JOIN portfolio p
ON u.uid=p.uid AND p.status=1
GROUP BY u.uid, u.username;
...which will give you...
+------+----------+------------------------------------------------------------------------------------------------------------------------------+
| uid | username | portfolios |
+------+----------+------------------------------------------------------------------------------------------------------------------------------+
| 1 | manoj | [{ "pid":2,"title":"title 2","status":1 },{ "pid":3,"title":"title 3","status":1 },{ "pid":1,"title":"title 1","status":1 }] |
| 2 | testing | [{ "pid":4,"title":"title 1","status":1 }] |
| 3 | user | NULL |
| 4 | company | NULL |
+------+----------+------------------------------------------------------------------------------------------------------------------------------+
MySQL 8: With JSON_OBJECTAGG and JSON_OBJECT functions
SELECT
u.user_id,
u.username,
CASE WHEN p.user_id IS NULL
THEN NULL
ELSE
JSON_OBJECTAGG(
IF(p.portfolio_id IS NULL, '', p.portfolio_id ),
JSON_OBJECT('title', p.title, 'status', p.status)) END portfolios
FROM user AS u
LEFT JOIN portfolio AS p ON u.user_id = p.user_id AND p.status = 1
GROUP BY u.user_id;
Result:
+---------+----------+--------------------------------------------------------------------------------------------------------------------------+
| user_id | username | portfolios |
+---------+----------+--------------------------------------------------------------------------------------------------------------------------+
| 1 | manoj | {"1": {"title": "title 1", "status": 1}, "2": {"title": "title 2", "status": 1}, "3": {"title": "title 3", "status": 1}} |
| 2 | testing | {"4": {"title": "title 1", "status": 1}} |
| 3 | user | NULL |
| 4 | company | NULL |
+---------+----------+--------------------------------------------------------------------------------------------------------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
