PHP SQL LEFT JOIN Statement lost one column

Question

I'm using the query suggested in this post https://stackoverflow.com/a/51816820/6822845 to list my table contents. This is working really fine, I get a list of every section with its subsections comma seperated in another column so I can explode it and convert it to an array. My problem is, that my sections table(mentioned under my other post linked above) has another column named "sorder" that holds the displaying order. I don't know why, but I'm not able to output it with the column selects as its every time 0.

SELECT sections.sorder as sorder ,section_titel as t1,
       GROUP_CONCAT(sub_section_titel) as t2 
FROM sections
    LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1
ORDER by sorder

Every time I run it sorder displays as "0". But it's not 0. The weird thing is, that I can read the "iorder" column which is in my sub_section table. But the "sorder"-column which is in the main-table "sections" isn't accessible / every time 0. I'm using Mysql:

SELECT sections.sorder as sorder section_titel as t1, you're lacking a comma after sorder — Tschallacka
– Tschallacka, Commented Aug 13, 2018 at 7:58
Oh i lost the comme while editing this text. But its in my code and there is to syntax error in it. Just the logic doesnt work. And i'm using mySQL — J. Doe
– J. Doe, Commented Aug 13, 2018 at 8:07

Nick · Accepted Answer · 2018-08-13 23:50:04Z

0

I've created an SQLFiddle which I hope represents your data. In that example, ORDER BY sorder works fine. I've also included an ORDER BY in the GROUP_CONCAT so that subsection titles can be ordered to. So, with the original query:

SELECT section_titel as t1, GROUP_CONCAT(sub_section_titel) as t2 
FROM sections LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1

The output is

t1              t2
Section One     SubOne,SubTwo
Section Three   SubThree
Section Two     (null)

But with the new query:

SELECT sorder, section_titel as t1, GROUP_CONCAT(sub_section_titel ORDER BY iorder) as t2 
FROM sections LEFT JOIN sub_sections ON section_id = sId
GROUP BY t1
ORDER BY sorder

The output is:

sorder  t1              t2
1       Section Three   SubThree
2       Section One     SubTwo,SubOne
3       Section Two     (null)

Note reordering of Section Three and Section One and also SubTwo and SubOne based on the values of sorder and iorder.

answered Aug 13, 2018 at 23:50

Nick

147k23 gold badges67 silver badges106 bronze badges

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

1 Comment

Nick Over a year ago

Did this help? If not, can you provide some more information (e.g. actual table structure since I have just guessed at it) to help solve the problem?

Collectives™ on Stack Overflow

PHP SQL LEFT JOIN Statement lost one column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related