I am having two tables named 'tbl_users' and 'tbl_order' where tbl_users having three columns 'id', 'name', 'items' where column 'items' data type is array and 'tbl_orders' having two columns 'id', 'item'. I want to select all the data and their orders using SELECT and JOIN. The end result should contain 'id', 'name', 'item' where column 'item' should contain all the items a user has ordered. Am using postgreSQL as database. Issue is I can join the tables but since the data type is array it ives error while performing JOIN. Anybody suggest a proper syntax for performing JOIN with array data type.
-
What does this have to do with PHP?– NickCommented Mar 8, 2019 at 11:36
-
stackoverflow.com/questions/2486725/…– FrankichCommented Mar 8, 2019 at 11:37
-
Possible duplicate of PostgreSQL JOIN with array type with array elements order, how to implement?– DaveCommented Mar 8, 2019 at 11:42
-
Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments)– user330315Commented Mar 8, 2019 at 11:55
-
Please provide the query you're having an issue with.– 404Commented Mar 8, 2019 at 11:55
|
Show 1 more comment
1 Answer
PostgreSQL supports array operators you can use to resolve the identifiers to their respective order item:
SELECT
u.id,
u.name,
CASE WHEN max(o.item) IS NULL THEN NULL ELSE array_agg(o.item) END AS items
FROM tbl_users AS u
LEFT JOIN tbl_orders AS o ON (u.items @> array[o.id])
GROUP BY 1, 2;
Online demo: https://rextester.com/CZDC2260
...would return:
id | name | items
----+------+-------
1 | john | {A,B}
2 | kate | {A}
3 | sue |
4 | ted |
(4 rows)
