SELECT stuff_id.Id, stuff_id.name,
   (select top 1 stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id
  order by  stuff_phone.preference) phone
FROM stuff_id

SELECT stuff_id.Id, stuff_id.name,
   (select stuff_phone.phone from stuff_phone where stuffid = stuff_id.Id
     order by stuff_phone.preference
     limit 1) phone
FROM stuff_id

SELECT st.Id, st.name, coalesce(max(ph1.phone),max(ph2.phone)) as phone
  FROM stuff as st
  LEFT JOIN stuff_phone ph1 ON st.id = ph1.id and ph1.preference='p'
  LEFT JOIN stuff_phone ph2 ON st.id = ph2.id and ph2.preference!='p'
 GROUP BY st.Id, st.name

SELECT st.Id, st.name,
       coalesce(max(case when ph1.preference='p' then ph1.phone end),
                max(ph1.phone)) as phone
  FROM stuff as st
  LEFT JOIN stuff_phone ph1 ON st.id = ph1.id
 GROUP BY st.Id, st.name

SELECT st.Id, st.name, ph.phone FROM stuff_id as st
INNER JOIN  stuff_phone as ph ON ph.stuffid = st.Id
WHERE ph.Id =
(SELECT Top 1 id from stuff_phone
WHERE st.Id = stuff_phone.stuffId
Order By stuff_phone.preference)
GROUP BY st.Id, st.name, ph.phone