1

I have two large tables:

products
--------
id (char) (primary key)
somefield1 (char)
somefield2 (char)

and

expired_products
------
id (char) (primary key)

I want to only have those ids which are in products but are not expired (id not in expired_products). In MS Access I would do something like this

SELECT products.*
FROM products LEFT JOIN expired_products ON products.id = expired_products.id
WHERE expired_products.id Is Null;

Ultimately, I want to write the result of the query into a new table.

Is there any better (faster query) solution in SQL Server Compact (sdf file)?

Btw. I am using C# and using the SqlCeConnection to access the database.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
Jasper
  • 660
  • 1
  • 7
  • 19

2 Answers2

4

The minus operator in Standard SQL and SQL Server is EXCEPT:

SELECT id 
  FROM products
EXCEPT
SELECT id 
  FROM expired_products;

...but it seems that EXCEPT is not supported in Compact 4.0 :(

As ever with SQL, that are alternative ways of performing semi difference:

e.g. NOT EXISTS 

SELECT *
  FROM products AS p
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM expired_products AS e
                    WHERE p.id = e.id
                  );

e.g. NOT IN 

SELECT *
  FROM products
 WHERE id NOT IN (SELECT id FROM expired_products);
Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
2
create table new_table (
    id char(7) -- same as primary key in products
)

This is command to execute in C#, it's "insert into ... select ..." statement:

insert into new_table (id)
select p.id
from products p
where not exists (select 1 from expired_products e.id = p.id)

Added after comment:

INSERT (SQL Server Compact) on MSDN

INSERT [INTO] 
      table_name  [ ( column_list ) ] 
      { VALUES 
      ( { DEFAULT | NULL | expression } [ ,...n] ) 
| derived_table
       }

From MSDN:

derived_table
Any valid SELECT statement that returns rows of data to be inserted into the table.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62