SQL Server – Where Clause with GUID Inequality Not Working

sql serveruuidwhere

I want to query our CRM Db.
I have a backup of CRM Db and the existing one.
There is a custom entity,in both of them I have created.
Some of my custom entity records has deleted from the existing db.
I want to compare which records has deleted.
Number of existing records is 17 and the backup contains 19 records.
The custom entity is named new_services.
The backup database is named test2 and the existing one is MSCRM The table name is new_services and
When I query this equal ones :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id = t2.new_service_Id;

And return correct records,
But when I try this :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id != t2.new_service_Id;

It returns 308 records !
The new_service_id is the guid of the record.
Where is wrong?

Best Answer

Your logic is wrong ;-) Assume two tables:

CREATE TABLE T1 ( x int not null );
INSERT INTO T1 (x) VALUES (1),(2),(3),(4);

CREATE TABLE T2 ( x int not null );
INSERT INTO T2 (x) VALUES (1),(12),(13),(14);

Your first example will return 1 row:

SELECT * 
FROM T1
JOIN T2
    ON T1.x = T2.x

1  1

whereas your second example will return 4*4 - 1 = 15 rows. If you think about it, in a set of 4x4=16 rows where the predicate is true for 1 row, how many rows if the predicate false for?

What you probably are looking for is MINUS or EXCEPT (not sure what it is called in sql-server):

SELECT * from test2.dbo.new_services
EXCEPT
SELECT * from MSCRM.dbo.new_services

or possibly the other way around (not sure which table is which)

Edit: As pointed out in the comments MySQL (not sure of latest version), does not support the basic set operations INTERSECT and EXCEPT. EXCEPT is SET MINUS and can be implemented using NOT EXISTS or NOT IN:

SELECT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

Another option is using an OUTER JOIN.

Worth noting is that the above corresponds to EXCEPT ALL since any duplicates in T1 is preserved. For a true EXCEPT operator, DISTINCT should be used:

SELECT DISTINCT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

I will not delve into the shadowlands of null, and the consequences it has for logical equality between expressions

INTERSECT on the other hand corresponds closely to multiplication. We can mimic that with the IN or EXISTS predicate in the same manner as above.

Related Solutions

Sql-server – Select MaxDate where hh:mm:ss makes the difference

In the OPENQUERY, you could get the max timestamp for all the dogs at once, and then join to the original PETS table to get the other columns, like so:

SELECT *
FROM [MyDatabase].[dbo].[DogTags] t1
JOIN OPENQUERY(ROPSA,
    'SELECT [TimeStamp], [Name], [Owner], [Address]
     FROM [CustomerDB].[PETS] t3
     JOIN (SELECT [Name], MAX(TimeStamp) AS TimeStamp
           FROM [CustomerDB].[PETS]
           GROUP BY [Name]) t4
     ON t3.[Name]=t4.[Name] AND t3.TimeStamp=t4.TimeStamp') t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.

Mysql – Query with WHERE NOT and NOT IN with subquery

Your unexpected results may be stemming from product_id being assigned to multiple categories in the oc_product_to_category table. If a product is in e.g. category 1 AND category 73, it will still be returned by the inner query and thus excluded from the outer query.

Produce a simple set of test data

INTO oc_product INSERT (product_id,  model, tax_class_id) VALUES
(1, 'model5', 10),
(2, 'AA', 5),
(3, '12', 8),
(4, '14', 12),
(5, 'xx', 12)

INTO oc_product_to_category INSERT (product_id, category_id) VALUES
(1, 1), -- product_id is in a category that is not in that list
(1, 73), -- product_id is STILL in a category that is not in that list; as well as a value in that list
(2, 73), -- product_id is only in categories in that list
(3, 17), -- product_id is only in categories not in that list
(4, 33), -- product_id is only in categories not on the category list, is excluded because of tax category
(5, 73), -- product_id is only in categories in the list, is excluded because of tax category

(I probably have the syntax of the INSERT query backwards;) )

I believe your current query will return

(2, 'AA', 5),

You probably want something like

SELECT 
    op.`product_id`,
    op.`model`,
    op.`tax_class_id` 
FROM
    oc_product AS op
  LEFT JOIN
    (SELECT DISTINCT p2c.product_id
     FROM oc_product_to_category as p2c
     WHERE
         p2c.category_id IN (73, 78, 116, 119, 120, 121, 122, 123, 125, 126, 127, 143, 159, 170, 176, 183, 211, 212, 213, 250, 260, 389, 433)
    ) as OnlyProductsBelongingToTheseIncluded
  ON op.product_id = OnlyProductsBelongingToTheseIncluded.product_id
WHERE 
    ( op.tax_class_id <> 12 ) AND
    OnlyProductsBelongingToTheseIncluded.product_id IS NOT NULL

This should return

(1, 'model5', 10),
(2, 'AA', 5)

3 is excluded because it only exists in other groups; 4 and 5 are excluded because of the tax code

Changing the last line to AllProductsBelongingToTheseExcluded.product_id IS NULL (and the appropriate subquery alias) should return only the (3, '12', 8) datapoint

Best Answer

Related Solutions

Sql-server – Select MaxDate where hh:mm:ss makes the difference

Mysql – Query with WHERE NOT and NOT IN with subquery

Related Question