sql - Comparing values from different views -

i got 6 views, want compare. easier this, lets bring 2 now.

views consists of 3 columns;

schema_name varchar(5) name varchar(250) hash_value varbinary(max) 

i want compare hashvalues between views.

then can inner join them that

select ch.name db1.dbo.v_tech_pvchecksums ch inner join db2.dbo.v_tech_pvchecksums au on au.hashvalue=ch.hashvalue  

and print names ch meet requirement set in join clause. doesn't work like to. problem is, when got matching rows in both views, there 1 row in au, doesn't have it's equivalent in ch table. query wont show row au table. tried right outer join, shows records au, doesn't meet condition.

output want achieve should looks that:

ch_name                         , au_name dq_1000_rundq                   , null                          <- don't match dq_1001_salesnullvalues2weeks   , dq_1001_salesnullvalues2weeks <- match dq_1002_salesdifference2weeks   , null                          <- dont null                            , somerecordwhichisonlyinau     <- row in au 

ch should kind of pattern comparing to, respecting above, when there no record in ch, in au

edit: sample data au:

dbo dq_1000_rundq                   0x5009848aa9bd90cd6c89ce99d3f8142e819279d1 dbo dq_1001_salesnullvalues2weeks   0x8838185422ebe35104dc92b24d898d54a97cf2ee dbo dq_1002_salesdifference2weeks   0xf24e2240de0f122a47425d1b4d878dac81aba596 dbo somerecordwhichisonlyinau       0xf24e2240de0f122a47425d1b4d878dac81aba596 

ch:

dbo dq_1000_rundq                   0x1a1605a5ff101ed0d6acfcfc996989d28dc92533 dbo dq_1001_salesnullvalues2weeks   0x8838185422ebe35104dc92b24d898d54a97cf2ee dbo dq_1002_salesdifference2weeks   0x4bcb9b19fdcd5409257a7375abf907e45c15509b 

as can see, second rows match themselves.

your question unclear, according desired output, need full outer join

select t.name,p.name db1.dbo.v_tech_pvchecksums t full outer join db2.dbo.v_tech_pvchecksums p on t.hashvalue=p.hashvalue  

after edit : if want is: when appear on both , equal -> take both, when appear on both hash not equal > take ch , when appear on ch - dont take , when appear on au > take it.

select case when p.name not null , p.hashvalue <> t.hashvalue null else t.name end name1        , p.name db2.dbo.v_tech_pvchecksums t left outer join db1.dbo.v_tech_pvchecksums p on t.name=p.name