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.
精彩评论