I have a table which has fields (id, parent_id, name, created_at). I want to write a query to select id, name, created_at, parent_created_at. The 'parent_created_at' is the 'created_at' field for the records matching parent (if it has one - i.e. the records parent_id >0), otherwise the 'parent_created_at' field should be null. Any suggestions?
A:
Check this: http://stackoverflow.com/questions/313383/ansi-92-recursive-sql-statement-required
This may also be useful to you: http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships
Mr. Smith
2009-08-11 09:55:36
A:
Self Join + ANSI SQL CASE Expression
SELECT t.id
, t.name
, t.created_at
, CASE WHEN t.parent_id > 0 THEN p.created_at ELSE NULL END AS parent_created_at
FROM Table t
JOIN Table p
ON t.id = p.parent_id
Svetlozar Angelov
2009-08-11 09:58:34
Not quite the result I expected:mysql> select t.id, t.title, t.created_at, u.name, CASE WHEN t.parent_id > 0 then p.created_at ELSE NULL END AS parent_created_at from user u, table t join table p on t.id=p.parent_id where t.creator_id=u.id;I have 2 records in table 'table' 1 with parent_ids of 0 and 1 respectively. I expected both rows to display. Only the row with parent_id=0 displays .. ? *scratches head*
2009-08-11 11:00:47
Geez. Instead of scratching your head, how about giving all the details for your two-row table? That should take about 10 seconds, and you won't waste everyone's time. (See my answer for a template on how you might provide this information.)
Steve Kass
2009-08-11 13:40:03
@Morpheous and @Svetlozar: (A LEFT OUTER JOIN is all you need, by the way, but it's still common courtesy to give some details.)
Steve Kass
2009-08-11 13:41:24
@Steve Kass you are right... I just don't know how is defined "missing parent"... If it is 0 and for some reason there is user with id = 0.... but you are right, my "defense" is useless
Svetlozar Angelov
2009-08-11 13:57:40
@Svetlozar: Thanks, and thanks to StackOverflow for the forum to clear it all up.
Steve Kass
2009-08-12 02:41:39
A:
Morpheous,
You only have two rows in your table, so please take the trouble to give all the details! All you tell us is what their parent_ids are, and your query's join condition depends on more than that.
Can you fill in the ... below so we know the full details?
CREATE TABLE t(...
INSERT INTO t VALUES (...
INSERT INTO t VALUES (...
Steve Kass
2009-08-11 13:37:32