Store array in sql query

Question

my question title may not really describe my problem sorry for that. I am undertaking database module. Thus, my sql query must be complicated and able to do in a single query

I have two table one is category

catid int
catname varchar

another table is product

productid int
catid references category(catid)
productname

now i want to do a select statement which select catname + an array of products under that cat, is it possible to do this in a single query like

SELECT a.catname,(b.productname bla bla in an array)
from category a, product b WHERE a.catid=b.catid

i dont know if this is possible, if yes please help me

What RDBMS? The solution is different for each because there is no ANSI SQL standard way — RichardTheKiwi
– RichardTheKiwi, Commented Mar 16, 2011 at 19:27

RichardTheKiwi · Accepted Answer · 2011-03-16 19:27:59Z

1

MySQL - use GROUP_CONCAT

SELECT a.catname, group_concat(b.productname) productnames
from category a
inner join product b on a.catid=b.catid
group by a.catname

answered Mar 16, 2011 at 19:27

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Casey · Accepted Answer · 2011-03-16 18:31:52Z

0

Look up the Inner Join keyword,

http://www.w3schools.com/sql/sql_join_inner.asp

This should be exactly what you need

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

answered Mar 16, 2011 at 18:31

Adam Casey

1,62014 silver badges22 bronze badges

1 Comment

bluedream Over a year ago

thanks, but if i use join, the result is fruit, apple; fruit, pear; but actually what i wanted is the result like fruit, [apple, pear]

N West · Accepted Answer · 2011-03-16 19:26:00Z

0

It depends on your database platform and where you're consuming the results of this query. Table columns can be created using Oracle SQL, as well as XML objects and the like, but it comes down to how you are consuming the data on the other end.

If you're processing this in a programming language, the JOIN method is likely the best and simplest method, and you can programatically load a data structure in your program.

answered Mar 16, 2011 at 19:26

N West

6,79428 silver badges40 bronze badges

Collectives™ on Stack Overflow

Store array in sql query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related