sql - WHERE - IN Query Shows Only Distinct Values -

i have temporary table insert values into, like;

select colourid #temp [orders] [order] = 12345 

then later on have statement this;

select colourname colours [id] in(select colourid #temp)  drop table #temp 

this return;

yellow red 

i know there several instances of red on order, , id red in temporary table each. how show each instance? i've tried performing count returns 1 beside each colour.

if understand correctly, want display, say, 'red' every order line contains red. need inner join:

select c.colourname, t.* #temp t inner join colours c on t.colourid = c.id