Store array in sql query

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 cate开发者_开发知识库gory

• 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

---

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

---

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

---

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.