Join MySQL

Question

I am trying to join 3 tables, somehow make a hierarchical inner join and get data from the third table. My starting point is the article number (156118) from the article table. Here are the working sql statements and the table structure, but there should be a way to combine all this together in one, right?

//  Get the parent task from the article
select task_parent
from article a, tasks t
where a.task_id = t.task_id
and a.article_number = 156118

// Get the task id for the 'Blog' task
select task_id 
from tasks 
where task_parent = 26093 
and task_name like '%blog%'

// Get ALL the blog record
select * 
from blogs
where task_id = 26091

---------Tables------------

* article table *
id | article_number | task_id
1  | 156118         | 26089


* tasks table *
id    | task_name | task_parent
26089 | article   | 26093 
26091 | blogs     | 26093
26093 | Main Task | 26093


* blog table *
id | task_id | content
1  | 102     | blah
2  | 102     | blah 
3  | 102     | blah

-------------

* How to get all blog data using 1 SQl statement using only article_number?

Thanks in advance!

Answer 1

EDIT : Revised answer after re-reading the question. You need two connections to the task table. One is to get the parent task of the article, and the second is the blog task with the same parent as the article task.

select b.id, b.task_id, b.content
    from article a
        inner join tasks t1
            on a.task_id = t1.task_id
        inner join tasks t2
            on t1.task_parent = t2.task_parent
                and t2.task_name like '%blog%'
        inner join blogs b
            on t2.task_id = b.task_id
    where a.article_number = 156118

Answer 2

, ... :

select b.* 
from blogs b, tasks t, tasks tp, article a  
where b.task_id = t.task_id 
and t.task_parent = tp.task_id 
and tp.task_id = a.task_id 
and a.article_number = 156118 

Answer 3

.

SELECT * FROM a
INNER JOIN tasks USING (a.article_number)
INNER JOIN blogs USING (a.article_number)
WHERE a.article_number = 156118;