SQL to select all siblings with specific child
In the WHERE you can simply filter for the parts which have an assemblie id equal to the assemblie id of 'Part B'

SELECT a.*, p.* 
FROM assemblies a
LEFT JOIN parts p USING (AID)
WHERE p.AID IN 
  (
    SELECT AID FROM parts WHERE P_NAME='Part B'
  );