Posted by Erland Sommarskog on 08/19/07 12:17

Erland Sommarskog (esquel@sommarskog.se) writes:
> Consider the script below.

Eh, what about also including the script:



CREATE TABLE countries
(coucode char(2) NOT NULL PRIMARY KEY,
couname varchar(30) NOT NULL,
is_eu bit NOT NULL)

INSERT countries (coucode, couname, is_eu)
EXEC('SELECT ''FI'', ''Finland'', 1;
SELECT ''SE'', ''Sweden'', 1;
SELECT ''NO'', ''Norway'', 0;
SELECT ''DK'', ''Denmark'', 1;
SELECT ''FO'', ''Faroe Islands'', 0;
SELECT ''IS'', ''Iceland'', 0;')

CREATE TABLE customers (custid int NOT NULL PRIMARY KEY,
name varchar(40) NOT NULL,
coucode char(2) NULL
REFERENCES countries(coucode))
INSERT customers (custid, name, coucode)
EXEC('SELECT 1, ''Pekka Hietaniemi'', ''FI'';
SELECT 2, ''Sven Svensson'', ''SE'';
SELECT 3, ''Geir Lindstad'', ''NO'';
SELECT 4, ''Anders And'', ''DK'';
SELECT 5, ''V U Hammerscheimb'', ''FO'';
SELECT 6, ''Einar Pálsson'', ''IS'';
SELECT 7, ''Krzystof Cibulski'', NULL;')
go
SELECT *
FROM customers
WHERE coucode NOT IN (SELECT coucode
FROM countries
WHERE is_eu = 0)

SELECT *
FROM customers cst
WHERE NOT EXISTS (SELECT *
FROM countries cou
WHERE cst.coucode = cou.coucode
AND cou.is_eu = 0)
go
DROP TABLE customers
DROP TABLE countries






--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Inconsistent identity values
• Prev in forum: Re: Inconsistent identity values
• Thread view: Re: How to check if a Table is <empty> ie doesnt contain any rows

[Reply to this message]