help building best query

I have a table structure that is basically

Sent_txts_parent

Sent_txts_control_parent

Sent_txts_control_child

the link together as

Sent_txts_parent = top level

Sent_txts_control_parent link to Sent_txts_parent via

Sent_txts_control_parent.parent_id = Sent_txts_parent.id

Sent_txt开发者_运维技巧s_control_child links to Sent_txts_control_parent via

Sent_txts_control_child.parent_id = Sent_txts_control_parent.id

I need to run a query which when given a Sent_txts_parent.id needs to return all rows from Sent_txts_control_child which are linked to that ID.

What would be the best way to do this query?

Possibly something like

Select stcc.* from Sent_txts_control_child stcc
left join Sent_txts_control_parent stcp
on stcc.parent_id = stcp.id
left join Sent_txts_parent stp
on stp.parent_id = stcp.parent_id;

But I dont even know if I have my joins o the right side, nor if that is the most optimized.

I don't see any reason to be using outer joins. The simple solution is:

SELECT cc.*
  FROM Sent_txts_control_child  AS cc
  JOIN Sent_txts_control_parent AS cp ON cc.Parent_ID = cp.ID
  JOIN Sent_txts_parent         AS st ON cp.Parent_ID = st.ID
 WHERE st.ID = ?;

However, we can observe that the value you are given corresponds to Sent_txts_parent.ID also matches Sent_txts_control_parent.Parent_ID, so there is no need for the triple-join; we can use a double-join instead:

SELECT cc.*
  FROM Sent_txts_control_child  AS cc
  JOIN Sent_txts_control_parent AS cp ON cc.Parent_ID = cp.ID
 WHERE cp.Parent_ID = ?;

The question mark represents the value that you are searching for.