Compare records in a database where one column exists and the other does not

Question

I am trying to compare two “lists” in one table and get records where customerIdexists but storeiddoes not exist for this customerId.

Lists (table definition)

name             listid   storeid   customerid

BaseList           1        10         100
BaseList           1        11         100
BaseList           1        11         102


NewList            2        11         100
NewList            2        12         102
NewList            2        12         103

Query:

SELECT 
    NewList.*
FROM 
    Lists NewList
LEFT JOIN 
    Lists BaseList ON BaseList.customerid = NewList.customerid 
WHERE 
    BaseList.listid = 1
    AND NewList.listid = 2
    AND NewList.storeid <> BaseList.storeid
    AND NOT EXISTS (SELECT 1 
                    FROM Lists c
                    WHERE BaseList.customerid = c.customerid
                      AND BaseList.storeid = c.storeid
                      AND c.listid = 2)

Current result:

NewList 2 11 100 
NewList 2 12 102 

But I expect to get the result.

NewList 2 12 102 

since client number 100 with storage 11 exists.

Fiddle

Answer 1

If the table definition contains a column Name(as you said), then the statement below returns your result.

I did not understand your select statement.

SELECT *
from @table
WHERE NAME = 'NewList'
  AND customerID IN (SELECT CustomerID FROM @table WHERE NAME = 'BaseList')
  AND storeID NOT IN (SELECT storeID FROM @table WHERE NAME = 'BaseList')

Answer 2

:

:

insert into Lists(name, listid, storeid, customerid) values('AnotherNew',3,11,100);
insert into Lists(name, listid, storeid, customerid) values('AnotherNew',3,11,102);
insert into Lists(name, listid, storeid, customerid) values('AnotherNew',3,10,100);

:

EDIT: - - , customerid storeid

DECLARE @listNames VARCHAR(MAX)=
STUFF(
    (
    SELECT DISTINCT ',[' + name + ']'
    FROM Lists
    FOR XML PATH('')
    ),1,1,'');

DECLARE @SqlCmd VARCHAR(MAX)=
'
WITH DistinctCombinations AS
(
    SELECT DISTINCT customerid,storeid
    FROM Lists AS l
)
SELECT p.*
FROM
(
SELECT DistinctCombinations.*
      ,OtherExisting.name AS OtherName
      ,CASE WHEN l.listid IS NULL THEN '''' ELSE ''X'' END AS ExistingValue
FROM DistinctCombinations
LEFT JOIN Lists AS l ON DistinctCombinations.customerid=l.customerid AND DistinctCombinations.storeid=l.storeid
OUTER APPLY
(
    SELECT x.name 
    FROM Lists AS x 
    WHERE x.customerid=l.customerid 
      AND x.storeid=l.storeid
) AS OtherExisting
) AS tbl
PIVOT
(
    MIN(ExistingValue) FOR OtherName IN (' +  @ListNames + ') 
) AS p';

EXEC(@SqlCmd);

customerid  storeid AnotherNew  BaseList    NewList
100            10      X           X         NULL
100            11      X           X         X
102            11      X           X         NULL
102            12      NULL        NULL      X
103            12      NULL        NULL      X

:

DECLARE @listNames VARCHAR(MAX)=
STUFF(
    (
    SELECT DISTINCT ',[' + name + ']'
    FROM Lists
    FOR XML PATH('')
    ),1,1,'');

DECLARE @SqlCmd VARCHAR(MAX)=
'
WITH DistinctLists AS
(
    SELECT DISTINCT listid
    FROM Lists AS l
)
SELECT p.*
FROM
(
SELECT l.*
      ,OtherExisting.name AS OtherName
      ,CASE WHEN l.listid IS NULL THEN '''' ELSE ''X'' END AS ExistingValue
FROM DistinctLists
INNER JOIN Lists AS l ON DistinctLists.listid= l.listid
CROSS APPLY
(
    SELECT x.name 
    FROM Lists AS x 
    WHERE x.listid<>l.listid 
      AND x.customerid=l.customerid 
      AND x.storeid=l.storeid
) AS OtherExisting
) AS tbl
PIVOT
(
    MIN(ExistingValue) FOR OtherName IN (' +  @ListNames + ') 
) AS p';

EXEC(@SqlCmd);

:

name      listid    storeid customerid  AnotherNew  BaseList    NewList
AnotherNew  3         10       100         NULL        X         NULL
AnotherNew  3         11       100         NULL        X         X
AnotherNew  3         11       102         NULL        X         NULL
BaseList    1         10       100         X           NULL      NULL
BaseList    1         11       100         X           NULL      X
BaseList    1         11       102         X           NULL      NULL
NewList     2         11       100         X           X         NULL

Answer 3

Tested this in Fiddle. Test it with a few more data. But for your input, it works correctly.

SQLFiddle Demo

SELECT c.*
FROM (
    SELECT n.*
    FROM (
        SELECT *
        FROM lists
        WHERE NAME = 'NewList'
        ) n
    LEFT JOIN (
        SELECT *
        FROM lists
        WHERE NAME = 'BaseList'
        ) b ON b.storeid = n.storeid
    WHERE b.listid IS NULL
    ) c
WHERE c.customerid IN (
        SELECT DISTINCT customerid
        FROM lists
        WHERE NAME = 'BaseList'
        )