select a.*,b.id from ##temptable a left outer join header b on a.style=b.styleno+'-'+b.colorcode and a.factory=b.factory where b.status='CONFIRMED'

create table #t1(id int, name varchar(10))
insert into #t1 
select 1,'Ken' union all
select 2,'David' union all
select 3,'Bradley' 

create table #t2(id int, class varchar(10))
insert into #t2 
select 1,'English' union all
select 1,'Math' union all
select 1,'Geo' union all
select 2,'English' union all
select 2,'Math' union all
select 2,'Geo' union all
select 2,'CS' 

select * from #t1 left join #t2
on #t1.id = #t2.id

/*
id    name    id    class
1    Ken    1    English
1    Ken    1    Math
1    Ken    1    Geo
2    David    2    English
2    David    2    Math
2    David    2    Geo
2    David    2    CS
3    Bradley    NULL    NULL

*/

------解决方案--------------------
而如果是INNER JOIN内联接的话，WHERE子句可以改写成AND，不会对最后的结果集的条数产生影响。而LEFT JOIN，RIGHT JOIN这两种联接，WHERE和AND是有区别的，WHERE会对AND产生的结果集再进行一次筛选。