0

I have 2 tables category and orderrows

The category table structure

id  name  parent_id
1   c1    null
2   c2   null
3   c3    2
4   c4    1
5   c5    2
6   c6    5
7   c7    5

Orderrow table structure

id  category  amount
1     1         100
2     6         100
3     5         100
4     4         100
5     3         100
6     2         100
7     7         100
8     2         100

I'm trying to join the 2 tables are get the total amount only on the parent level

Result:

category  total
1         200
2         600

Is there a way to write sql in MySQl to obtain the result.

The results basically gets all the parent child relationships and prints the total amount only on parent level.

takethat
  • 1
  • In MySQL 8.0, yes. In MySQL 5.x, you're out of luck. – The Impaler May 31 '18 at 15:28
  • There is a method @TheImpaler in MySQL 5.5+ because of the user variables .But is pretty tricky https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Raymond Nijland May 31 '18 at 15:30
  • 1
    See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry May 31 '18 at 15:31
  • @Raymond: as long as it doesn't use variables. Those are tricky to write and debug. Plus those queries are very inefficient on large row sets. – The Impaler May 31 '18 at 15:31
  • 1
    @TheImpaler No they're not. Besides, that wasn't the nub of your original argument - I agree that 8.0 has considerably simplified this process. – Strawberry May 31 '18 at 15:35
  • @Strawberry Don't want to open a "religious war" here, but there's no way of establishing a "stop condition" when using variables. You're at the mercy of the table rows there. – The Impaler May 31 '18 at 15:37
  • 1
    "Plus those queries are very inefficient on large row sets."In MySQL 5.5/5.7 I would rather use a MySQL user variable to simulate ranking (`ROW_NUMBER()`) then a co-related subquery with COUNT() on large datasets to give you a example..@TheImpaler "Those are tricky to write and debug" That's true you need to know how to write and use them properly if you know how they work it's just like writing `(1 + 1) AS alias` – Raymond Nijland May 31 '18 at 16:06

0 Answers0