I've following data in mySQL:(This is Comment data)
and then I wanna group those data by ucom_ucom_id, and it'll be like this
UCOM_ID | USER_ID | UCOM_DATE | UCOM_COMMENT | UCOM_TITLE | UCOM_UCOM_ID
---------------------------------------------------------------------------
7 | 2 | 2012-01-28 | xxx | xxx | 0
8 | 2 | 2012-01-28 | xxx | xxx | 7
12 | 2 | 2012-01-28 | xxx | xxx | 7
9 | 2 | 2012-01-28 | xxx | xxx | 0
13 | 2 | 2012-01-28 | xxx | xxx | 9
Which UCOM_UCOM_ID = 0, is first comment
, and if UCOM_UCOM_ID = one of UCOM_ID
, then it is comment from first comment.
I would like to produce view in php like this:
[UCOM_ID] 7, [UCOM_COMMENT] xxx
[UCOM_ID] 8, [UCOM_COMMENT] xxx
[UCOM_ID] 12, [UCOM_COMMENT] xxx
[UCOM_ID] 9, [UCOM_COMMENT] xxx
[UCOM_ID] 13, [UCOM_COMMENT] xxx
What query in mySql to get the result above, or should I do anything else without using query,.?
Many Thanks,.
It's a little tough to tell exactly what you intend the result set to look like. I think this will probably do what you want though:
SELECT y.UCOM_ID, y.UCOM_COMMENT
FROM tablename x
LEFT JOIN tablename y
ON y.UCOM_UCOM_ID = x.UCOM_ID
OR
(y.UCOM_UCOM_ID = 0
AND
y.UCOM_ID = x.UCOM_ID)
WHERE x.UCOM_UCOM_ID = 0