"Exclude" Joint? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"Exclude" Joint?

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-01-22 : 14:37:55

Hi,

I have 2 tables like the following:

CREATE TABLE #tblAllShapes (shapeID smallint PRIMARY KEY IDENTITY NOT NULL, shape nvarchar (10) NOT NULL, color nvarchar(10) NULL);

INSERT INTO #tblAllShapes (shape, color) VALUES ('square', 'red');
INSERT INTO #tblAllShapes (shape, color) VALUES ('circle', 'blue');
INSERT INTO #tblAllShapes (shape, color) VALUES ('triangle', 'green');
INSERT INTO #tblAllShapes (shape, color) VALUES ('square', 'blue');
INSERT INTO #tblAllShapes (shape, color) VALUES ('circle', 'yellow');

CREATE TABLE #tblBadShapes (shapeID smallint PRIMARY KEY IDENTITY NOT NULL, shape nvarchar (10) NOT NULL, color nvarchar(10) NULL);

INSERT INTO #tblBadShapes (shape, color) VALUES ('square', 'red');
INSERT INTO #tblBadShapes (shape, color) VALUES ('circle', 'blue');
INSERT INTO #tblBadShapes (shape, color) VALUES ('triangle', 'blue');

How do I select all the entries in #tblAllShapes which don't exist in #tblBadShapes (that is only the green triangle, blue square and yellow circle)?

Thanks,
ywb

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 14:44:26

select all.*
from #tblallshapes as all
left join #tblbadshapes as bad on bad.shape = all.shape and bad.color = all.color
where bad.shape is null

Peter Larsson
Helsingborg, Sweden

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-01-22 : 16:17:51

Thanks, Peso! That works.

It's good to see it work, but I don't understand how it works just by adding a filtering for "bad.shape IS NULL".

Also, if the tables were to allow NULL for shape and both had some entries with NULL as shape:

CREATE TABLE #tblAllShapes (shapeID smallint PRIMARY KEY IDENTITY NOT NULL, shape nvarchar (10) NULL, color nvarchar(10) NULL);

INSERT INTO #tblAllShapes (shape, color) VALUES ('square', 'red');
INSERT INTO #tblAllShapes (shape, color) VALUES ('circle', 'blue');
INSERT INTO #tblAllShapes (shape, color) VALUES ('triangle', 'green');
INSERT INTO #tblAllShapes (shape, color) VALUES ('square', '');
INSERT INTO #tblAllShapes (shape, color) VALUES ('circle', 'yellow');
INSERT INTO #tblAllShapes (shape, color) VALUES (NULL, 'red');

CREATE TABLE #tblBadShapes (shapeID smallint PRIMARY KEY IDENTITY NOT NULL, shape nvarchar (10) NULL, color nvarchar(10) NULL);

INSERT INTO #tblBadShapes (shape, color) VALUES ('square', 'red');
INSERT INTO #tblBadShapes (shape, color) VALUES ('circle', 'blue');
INSERT INTO #tblBadShapes (shape, color) VALUES ('triangle', 'blue');
INSERT INTO #tblAllShapes (shape, color) VALUES (NULL, 'blue');

That solution wouldn't work anymore. What's happening here?

jayp369
Starting Member

26 Posts

Posted - 2007-01-22 : 17:19:08

Or..

SELECT * FROM #tblAllShapes
WHERE shape+color NOT IN (SELECT shape+color FROM #tblBadShapes)

Jay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 00:46:06

Jay, when concatening a NULL value with a string, results in a NULL value

Ywb, if that requirement was known from the beginning, why didn't you tell us from the start?

select all.*
from #tblallshapes as all
left join #tblbadshapes as bad on bad.shape = all.shape and bad.color = all.color
where bad.shape is null and bad.color is null

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 01:04:48

Or try this for comaprison.
Since there is a record in badshapes {null, blue}, the algorithm below trets all blue colors as invalid.

create table #tblallshapes (shapeid smallint primary key identity, shape nvarchar(10), color nvarchar(10))

insert into #tblallshapes (shape, color) values ('square', 'red')
insert into #tblallshapes (shape, color) values ('circle', 'blue')
insert into #tblallshapes (shape, color) values ('triangle', 'green')
insert into #tblallshapes (shape, color) values ('square', '')
insert into #tblallshapes (shape, color) values ('circle', 'yellow')
insert into #tblallshapes (shape, color) values (null, 'red')

create table #tblbadshapes (shapeid smallint primary key identity, shape nvarchar(10), color nvarchar(10))

insert into #tblbadshapes (shape, color) values ('square', 'red')
insert into #tblbadshapes (shape, color) values ('circle', 'blue')
insert into #tblbadshapes (shape, color) values ('triangle', 'blue')
insert into #tblallshapes (shape, color) values (null, 'blue')

select		a.*
from		#tblallshapes as a
left join	#tblbadshapes as b on coalesce(b.shape, a.shape, char(3)) = coalesce(a.shape, b.shape, char(3))
			and coalesce(b.color, a.color, char(3)) = coalesce(a.color, b.color, char(3))
where		a.shape is not null
		and b.shape is null

drop table #tblallshapes
drop table #tblbadshapes

Peter Larsson
Helsingborg, Sweden

ywb
Yak Posting Veteran

55 Posts

Posted - 2007-01-23 : 10:36:43

Thanks guys.

Well Peter, that wasn't a requirement. I was just curious how your first solution could be modified to handle a slightly different situation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources