sql server - Get records where one column is same but another column is different -

i'm trying compare 2 "lists" in same table , records customerid column has same value storeid different.

lists (table definition)

name             listid   storeid   customerid  comparinglist1     1        10         100 comparinglist1     1        10         101 comparinglist1     1        11         100 comparinglist1     1        11         102 comparinglist1     1        11         103 comparinglist1     1        11         104  comparinglist2     2        10         100 comparinglist2     2        10         101 comparinglist2     2        11         100 comparinglist2     2        11         102 comparinglist2     2        11         103 comparinglist2     2        12         104 comparinglist2     2        12         105 

query

select     comparinglist2.customerid customerid,    comparinglist1.storeid expectedstoreid,     comparinglist2.storeid actualstoreid lists comparinglist2 left join lists comparinglist1    on comparinglist1.customerid = comparinglist2.customerid     comparinglist1.listid = 1    , comparinglist2.listid = 2    , comparinglist2.storeid <> comparinglist1.storeid 

this query gives me:

customerid   expectedstoreid   actualstoreid    100             10                 11     100             11                 10     104             11                 12  

but result want is:

customerid         expectedstoreid      actualstoreid    104                   11                   12 

fiddle

well, close. i've added condition clause gives desired result, i'm not sure how perform on actual data.

here modified query:

select    comparinglist2.customerid customerid,   comparinglist1.storeid expectedstoreid,    comparinglist2.storeid actualstoreid lists comparinglist2 left join lists comparinglist1   on comparinglist1.customerid = comparinglist2.customerid  comparinglist1.listid = 1 , comparinglist2.listid = 2 , comparinglist2.storeid <> comparinglist1.storeid , not exists -- added part (     select 1      lists c     comparinglist1.customerid = c.customerid     , comparinglist1.storeid = c.storeid     , c.listid = 2 )