1

This is similar to my table:

| id | parent_id | title        |
|----|-----------|--------------|
| 61 | NULL      | title of 61  |
| 62 | 61        | another title|
| 68 | 61        | more text    |
| 75 | 61        | testing 123  |

Let's say I select ID 61. I want this output (all children of parent):

| id | title        |
|----|--------------|
| 62 | another title|
| 68 | more text    |
| 75 | testing 123  |

so, i use the following mysql:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON a2.parent_id = a.id
WHERE a.id = 61

...so far so good...

Let's say I select ID 75, I want this output (parent and siblings):

| id | title        |
|----|--------------|
| 61 | title of 61  |
| 62 | another title|
| 68 | more text    |

so, i use this query:

SELECT a2.id, a2.title
FROM article a
JOIN article a2 ON COALESCE(a2.parent_id, a2.id) = a.parent_id
WHERE a.id = 75 AND a2.id != 75

Is there a way to create a combination of those 2 queries so whatever ID i pass, it will output correctly...

NOTE: English is not my first language, please feel free to ask questions if its not clear

  • So you always want parent, siblings and children? – Lennart Dec 31 '18 at 20:19
  • @Lennart Basically, that's what I'm looking for – Marco Dec 31 '18 at 20:28
2

Your first query can be simplified to:

SELECT a2.id, a2.title 
FROM article a2
WHERE a2.parentid = 61

To combine this with your second query you can use union:

SELECT a2.id, a2.title 
FROM article a 
JOIN article a2 
    ON COALESCE(a2.parent_id, a2.id) = a.parent_id 
WHERE a.id = 61 AND a2.id <> 61

UNION

SELECT a2.id, a2.title 
FROM article a2
WHERE a2.parentid = 61;

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.