Why is SQL so bad at trees...

Why is SQL so bad at trees? I need all data that matches criteria A and all sub-data that is associated with aforementioned data that matches criteria B.
This is extremely hard to model and the result set is awkward work with after fetching.
Who came up with this shit?

Attached: 1508002545795.jpg (240x240, 12K)

SELECT *
FROM criteriaA
LEFT JOIN criteriaB ON criteriaA.subdata = criteriaB.subdata

Am I missing something or is OP being retarded with his vague example?

OP is always retarded

Consider the following:
You have a table of threads.
You have a table of posts.
Posts are linked to threads via foreign keys.
Getting the top 10 most recently bumped and not deleted (criteria A) threads and the first as well as the last 10 posts (criteria B) gives you this beauty of a query with four fucking subqueries and a CTE:
WITH target_threads AS (
SELECT thread_id, MAX(post_id) AS max_post_id FROM threads JOIN posts ON posts.thread = threads.thread_id WHERE threads.is_deleted = false AND board = 1 GROUP BY thread_id ORDER BY last_bump DESC LIMIT 10
)
SELECT t.*, p.*
FROM threads AS t
LEFT JOIN posts AS p
ON p.thread = t.thread_id
WHERE p.thread IN (SELECT thread_id FROM target_threads)
AND (p.post_id = 1 OR p.post_id >= (SELECT max_post_id FROM target_threads WHERE thread_id = t.thread_id) - 10)
ORDER BY t.last_bump DESC, post_id ASC;
And that's ignoring the issue that you now have all the thread data duplicated and need a loop to tell when a thread post list ends because there are no fucking trees.

I can't see your tables you dumbass.

describe tables, write them on the paper and draw the quiery with pen. then convert that to sql.

That's my point. You can't draw it because it's a tree, not a table.

>tree

I don't understand what you are trying to achieve. But if so use shit like connect by

What in the fuck are you talking about?
If you're looking to get one row back for 1:N joins, you could use array_agg() in Postgres. If you want completely denormalized document storage, then get an extra chromosome and use MongoDB. Good luck shooting yourself with that schema-less engine.

I figured out he wants something like this:
thread 1 (not deleted) data
---post 1 data
---post 21 data
---post 22 data
...
---post 30 (last) data

thread 2 (not deleted) data
---post 1 data
---post 45 data
---post 46 data
...
---post 54 (last) data

...

thread 10 (not deleted) data
---post 1 data
---post 79 data
---post 80 data
...
---post 88 (last) data

ok, what's the point of "MUH TREE" than? just fpfb

this looks like situation when you use 'rollup'

Ok so you get duplicaties?
That's the problem?

Then you have to do a query to check if there are duplicates then delete.

I think i have seen this problem before.

But then I lose the associated posts.

Then you are doing something wrong with your Insert query.

Maybe check first if thread exists return true.
then just insert into the Post fk.

Its pretty hard to see how you create your threads to begin with.
And how you INSERT.

Pro-tip: You can run more than 1 query.

> Why is SQL so bad at trees?
Indexing works with B-trees, so not that bad.

I think you're talking about two different things. means that when you execute his query thread data duplicates with each post like this: while he would like it to look like an array of arrays where outer array contains threads and inner arrays contain posts of each thread. Basically he complains about the way sql (or should I say relational db?) returns a set of joined tables with data from the first table repeating with each associated row from the second table.

Ah yea now i understand yes i had this too.

I don't know but rational databases can act really weird with these LEFT/Right joins sometimes.

Think i solved it by ditching that and just going Inner join.

Show us the query you're using, and what db

Perhaps you just need to git gud

Format your SQL man, what is this dogshit

Think he is forgetting GROUP BY

use tree datatype in postgres nigger