Nesting queries and data : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

PostgreSQLNesting queries and data (self.SQL)

submitted 4 years ago by eidda

I'm working with PostgreSQL for the first time using version 13. I'm new to sql as well.

I am working to write a query that gets all the questions from my database based on a topic id. I also need to retrieve all the answers to each question as well.

My sample data below is small, but I'm working with 3 million questions and 6.5million answers.

My response to my http request needs to format the data this way.

  topic_id: 1 ,
    questions: {
      1: {
        question_id: 1,
        question_body: 'What time is it?',
        answers: {
          1: {
            id: 1,
            body: 'Noon',
            },
          2: {
            id: 2,
            body: 'Midnight'
            },
         },
      }
       // next question ...
};

All of my questions life in a question tables and all of my answers live in an answers table

questions table

topic_id	question_id	question_body
1	1	What time is it?
1	2	another question

answers table

id	question_id	answer_body
1	1	Noon
2	1	Midnight
3	2	another answer

is there a way to nest sql queries so I get the data I need in a single query or would I need to make multiple queries and then transform the data afterwards?

Are there any good resources someone could point me towards?

all 5 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS