sql - Conditionally Select additional rows from another table -

i have 2 tables:

tablea pk      alpha    critter   etc  1             zebra     apple 2        c      lion      orange 3        r      giraffe   banana 4        d      gopher    pear  tablec pk      alpha   animal    misc 1       d       beaver    kiwi 2       d       camel     avacado 

i need result:

result pk      alpha    critter  etc 22            zebra    apple 23      c       lion     orange 24      r       giraffe  banana 25      d       gopher   pear 26      d       beaver   kiwi 27      d       camel    avacado 

if value of tablea.alpha equals more 1 tablec.alpha same value select be:

select ta.alpha, ta.critter, ta.etc tablea ta 

and

select tc.alpha, tc.animal, tc.misc tablec tc 

on separate rows (3 rows in above example)

otherwise just

select ta.alpha, ta.critter, ta.etc tablea ta 

(one row)

i might use cte. like:

with t_cte (alpha,countalpha) (select  tc. alpha, count(tc.alpha) countalpha tablec tc group tc.alpha having (count(tc.alpha) > 1 ))  select ta.alpha, ta.critter, ta.etc tablea ta case when t_cte.alpha not null then...else...end left join t_cte on t_cte.alpha = ta.alpha 

tablea.alpha unique. tablec.alpha not unique.

am able call select addtional rows? there better approach? i've thought of creating temp tables , selecting them. part of larger query, want focus on part now. suggestions? thanks.

something like

;with cte (   select a.alpha, count(*) cnt   tablea       inner join tablec c on c.alpha = a.alpha   group a.alpha   having count(*) > 1 ) select a.alpha, a.critter, a.etc tablea union select c.alpha, c.animal, c.misc tablec c c.alpha in (select t.alpha cte t) 

sql fiddle demo - i've added 1 row tablec check if include it