ANSI SQL question

Question

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?

Svetlozar Angelov · Accepted Answer · 2009-08-11T10:06:20.780

0

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

edited Aug 11 '09 at 10:06

answered Aug 11 '09 at 09:58

Svetlozar Angelov

21,214
6
62
67

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 p arent_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* – Aug 11 '09 at 11:00
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 Aug 11 '09 at 13:40
@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 Aug 11 '09 at 13:41
@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 Aug 11 '09 at 13:57
@Svetlozar: Thanks, and thanks to StackOverflow for the forum to clear it all up. – Steve Kass Aug 12 '09 at 02:41

score 0 · Answer 2 · answered Aug 11 '09 at 13:37

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 (...

ANSI SQL question

2 Answers2