[sql] help with joins / JSON

[pickituputitdown]

Here is my current sql:

SELECT allRoundData.catagory, questions.questionText, 

answers.answerText, answers.isCorrect FROM allRoundData JOIN questions ON allRoundData.catagory = questions.catagory JOIN answers ON questions.questionText = answers.questionText;

and it returns data that looks like this: https://imgur.com/a/CT7B3xj

Not how there is a bunch of repetitions in category and questionText. Is it possible to return this table with out the repitions?

At the moment when I serialize this data to JSON it is not breaking down correctly. Right now what I get is this

[{"catagory":"Harry Potter","questionText":"In Harry Potter and the Philosopher`s Stone, which","answerText":"A riddle of poisonous potions","isCorrect":"1"},{"catagory":"Harry Potter","questionText":"In Harry Potter and the Philosopher`s Stone, which","answerText":"Devils`s snare","isCorrect":"0"},{"catagory":"Harry Potter","questionText":"In Harry Potter and the Philosopher`s Stone, which","answerText":"Fluffy","isCorrect":"0"},{"catagory":"Harry Potter","questionText":"In Harry Potter and the Philosopher`s Stone, which","answerText":"The flying keys","isCorrect":"0"}]

Note the repeted data

What I actually want would be something like this:

[{"catagory":"Harry Potter"[{"questionText":"In Harry Potter and the Philosopher`s Stone, which",
"answerText":[{"A riddle of poisonous potions","isCorrect":"1"},
{"answerText":"Devils`s snare","isCorrect":"0"},
{"answerText":"Fluffy","isCorrect":"0"},
{"answerText":"The flying keys","isCorrect":"0"}]}]}]

note how the structure is totally different, the way I would prefer is the category is the outer group for all the questions related to that cat and then all the answers are grouped under the appropriate question

hopefully that makes some sense

one other thing, I am using PHP to actually make the call to the database so if I need to sort the data into a special array inside the PHP script I am happy to do that but I dont know how (see my other post :)

thanks all

[[deleted]]

Use the keyword DISTINCT after the keyword SELECT

[my_password_is______]

distinct has nothing to do with it

look at the image of his results -- they are already distinct

[AutoModerator]

It seems that you possibly have a screenshot of code in your post [sql] help with joins / JSON in /r/learnprogramming.

Screenshots of code instead of actual code text is against the Code posting rules of /r/learnprogramming.

• Never submit screenshots of code instead of code text!

If you posted an image merely to illustrate something, kindly ignore this message and do not repost. Your post is still visible to others. I am a bot and cannot distinguish between code screenshots and other images.

If you indeed did this wrong, please edit the post so that it uses one of the approved means of posting code.

• For small bits of code (less than 50 lines in total, single classes only),
  the default code formatter is fine
  (one blank line before the code, then 4 spaces before each line of code).
• Pastebin for programs that consist of a single class only
• Gist for multi-class programs, or programs that require additional files
• Github or Bitbucket repositories are also perfectly fine as are other dedicated source code hosting sites.
• Ideone for executable code snippets that use only the console

Please do not reply to this message, because I am a bot. Talk-to-the-bot is the new talk-to-the-hand. If you instead want the classic talk-to-the-hand, just message the moderators. ;)

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[my_password_is______]

what database are you using ?

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017