Which items are not linked??
-
I'm a bit braindead today and this has been burning my already overtaxed brain cells. Environment is SQL 2000 sp3a and W2k.
I've got two tables listing items.
Table1 ItemID ItemName 1 AAA 2 BBB 3 CCC 4 DDD Table2 WidgetID WidgetName WidgetType 10 ZZZ A 20 YYY A 30 XXX B 40 WWW A 50 VVV C 60 UUU B 70 TTT A 80 SSS C I've got a third table that provides a link between the two.
Table3 ItemID WidgetID 1 10 1 20 2 10 2 20 Now here's the question, if the input is ItemName = 'AAA', what widgets of the same type aren't linked?
Answer should be Widgets 'WWW' and 'TTT'
I've been back and forth over this one so many times now that I'm just confusing myself more.
--------------------
Colt 45 - the original point and click interface
-
select a.widgetname, widgettype
into #aa
from table2 a
inner join table3 b on a.widgetid = b.widgetid
inner join table1 c on b.itemid = c.itemid and c.itemname='AAA'
select * from table2 a
where exists(select * from #aa b where a.widgettype=b.widgettype)
and not exists(select * from #aa c where a.widgetname = c.widgetname)
-
select #table1.*, #table2.* from #table1 cross join #table2
left outer join #table3 on #table1.ItemID = #table3.ItemID and #table2.WidgetID = #table3.WidgetID
where #table1.ItemName = 'AAA'
and #table3.ItemID is null
and #table2.WidgetType in(
select #table2.WidgetType from #table2
join #table3 on #table2.WidgetID = #table3.WidgetID
join #table1 on #table1.ItemID = #table3.ItemID )
/*
create table #table1 ( ItemID int not null primary key, ItemName char(3) not null )
insert #table1 select 1,'AAA' union select 2,'BBB' union select 3,'CCC' union select 4,'DDD'
create table #table2 ( WidgetID int not null primary key, WidgetName char(3) not null, WidgetType char(1) not null )
insert #table2 select 10,'ZZZ','A' union select 20,'YYY','A' union select 30,'XXX','B' union select 40,'WWW','A'
union select 50,'VVV','C' union select 60,'UUU','B' union select 70,'TTT','A' union select 80,'SSS','C'
create table #table3 ( ItemID int not null, WidgetID int not null, primary key(ItemID,WidgetID) )
insert #table3 select 1,10 union select 1,20 union select 2,10 union select 2,20
*/
/rockmoose
You must unlearn what You have learnt -
Correction:
select #table1.*, #table2.* from #table1 cross join #table2
left outer join #table3 on #table1.ItemID = #table3.ItemID and #table2.WidgetID = #table3.WidgetID
where #table1.ItemName = 'AAA'
and #table3.ItemID is null
and #table2.WidgetType in(
select #table2.WidgetType from #table2
join #table3 on #table2.WidgetID = #table3.WidgetID
join #table1 on #table1.ItemID = #table3.ItemID
where #table1.ItemName = 'AAA' )
You must unlearn what You have learnt -
Good solution.
Funnily enough, after working on something else for a day, I came back to this and knocked up a similar query. Amazing what a clear head can do
I like your query though as it eliminates the use of temp tables. One change I did make was to put the in ( ... ) subquery into FROM clause as a derived table. This is probably more cosmetic given the small volume of data I'm dealing with.
--------------------
Colt 45 - the original point and click interface
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply