correctly fetch nested list in SQL

Question

I have a design problem with SQL request:

I need to return data looking like:

listChannels:
    -idChannel
     name
     listItems:
        -data
        -data
    -idChannel
     name
     listItems:
        -data
        -data

The solution I have now is to send a first request: *"SELECT * FROM Channel WHERE idUser = ..."* and then in the loop fetching the result, I send for each raw another request to feel the nested list: "SELECT data FROM Item WHERE idChannel = ..." It's going to kill the app and obviously not the way to go.

I know how to use the join keyword, but it's not exactly what I want as it would return a row for each data of each listChannels with all the information of the channels.

How to solve this common problem in a clean and efficient way ?

In SQL you are working with tabular data, it's just the way it is. If you want to return the data in such structure consider using document database or some other kind of NoSQL database. — krivtom
– krivtom, Commented Nov 9, 2014 at 12:02
Often the best way to approach this is to return all the nodes as rows in 1 select statement, but add something like a ParentIdChannel column, then use that to construct the tree in the calling code (C#, PHP etc). The select statement you use to return the relevant nodes depends on which database engine you are using (MySQL, SQL Server), so add the relevant tag for it to the question and we may be able to help you with it. — Rhumborl
– Rhumborl, Commented Nov 9, 2014 at 12:06
Add the RDBMS (SQL Server? what version) and we can probably build a SQL statement that returns exactly that dataset — Nick.Mc
– Nick.Mc, Commented Nov 9, 2014 at 12:23
I'm working with mysql, I though the SQL protocol was standard among every server. So the idea is to make only 2 request, one with channels and one with the items of all channel, and then sort that in the application code ? — Titan
– Titan, Commented Nov 9, 2014 at 13:27

Gordon Linoff · Accepted Answer · 2014-11-09 12:42:09Z

1

The "SQL" way of doing this produces of table with columns idchannel, channelname, and the columns for item.

select c.idchannel, c.channelname, i.data
from channel c join
     item i
     on c.idchannel = i.idchannel
order by c.idchannel, i.item;

Remember that a SQL query returns a result set in the form of a table. That means that all the rows have the same columns. If you want a list of columns, then you can do an aggregation and put the items in a list:

select c.idchannel, c.channelname, group_concat(i.data) as items
from channel c join
     item i
     on c.idchannel = i.idchannel
group by c.idchannel, c.channelname;

The above uses MySQL syntax, but most databases support similar functionality.

answered Nov 9, 2014 at 12:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

michi.0x5d · Accepted Answer · 2014-11-09 12:07:22Z

1

SQL is made for accessing two-dimensional data tables. (There are more possibilities, but they are very complex and maybe not standardized)

So the best way to solve your problem is to use multiple requests. Please also consider using transactions, if possible.

answered Nov 9, 2014 at 12:07

michi.0x5d

1,00910 silver badges13 bronze badges

Collectives™ on Stack Overflow

correctly fetch nested list in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related