0

Thank you guys in advance! i am desperately need to display my data in this way,

enter image description here

My database structure is,

enter image description here

Column idcat, parent_id and child_id is sharing single column name, catname.

I tried to write my own query,

SELECT idcat, parent_id`, child_id, catname, catDesc, catstat, del_cat 
(SELECT catname WHERE parent_id=idcat FROM categories ) as parent, 
(SELECT catname WHERE child_id=`parent_id FROM categories) as child 
FROM categories

I hope you can help me guys! thank you!

0

Use aliases like that:

Select a.Column, b.Column
FROM TableA AS a
JOIN TableB AS b on a.TableBId = b.Id

This way the engine knows when you want to use Column from TableA and when from TableB.

It is a good practice - you should always use aliases when writing SQL queries.

The query you are looking for might be:

SELECT c.catname
    ,(
        SELECT c1.catname + coalesce('>' + cast(
                    SELECT c2.child_id
                    FROM categories c2
                    WHERE c2.idcat = c1.child_id
                    ) AS char(50)), '')
        FROM categories c1
        WHERE c.parent_id = c1.idcat
        )
    ,c.catDesc
    ,c.catstat
FROM categories c
  • Thank you @Lukasz! Can you show me how to apply it in a single table as I shown above? – Lito Mar 14 '15 at 11:07
  • @LitoLozada I just posted a query you might be looking for. Try it and let me know if it returns the data you need. – BuahahaXD Mar 14 '15 at 12:12
  • Hi @Lukasz it says Truncated incorrect DOUBLE value: '>' – Lito Mar 15 '15 at 2:06
  • I just added the cast() function to the code. Try it out. if it doesn't work, please post a the full error. – BuahahaXD Mar 16 '15 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.