Avoiding large number of self joins

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)

Avoiding large number of self joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-21 : 07:23:39

David writes "Hi,

We have the following database setup:


Printer
  ID
Feature 
  ID
  NAME
  PRINTER_ID - foreign key Printer.id
Setting 
  ID
  NAME
  VALUE
  FEATURE_ID - foreign key Feature.id

So a Printer can have any number of Features, each of which can have any number of Settings.

Now, the kicker. We need to find all the Printers which match a filter based on 7 fields - effectively Feature.name, Setting.name and Setting.value combinations. For example, find all the Printers which have (I'll limit to 5 fields here):


   Feature.name="network" AND Setting.name="IpAddress" AND Setting.value="157.184.70.2"
AND
   Feature.name="network" AND Setting.name="hostname" AND Setting.value="myhostname"
AND
   Feature.name="network" AND Setting.name="contact" AND Setting.value="mycontact"
AND
   Feature.name="network" AND Setting.name="location" AND Setting.value="mylocation"
AND
   Feature.name="print" AND Setting.name="pageCount" AND Setting.value="1"

To do this, I had to do multiple, ugly, self joins, as follows:


SELECT * FROM 
   PRINTER P INNER JOIN FEATURE F1 ON P.ID=F1.PRINTER_ID 
             INNER JOIN FEATURE F2 ON P.ID=F2.PRINTER_ID 
             INNER JOIN SETTING S1 ON F1.ID=S1.FEATURE_ID 
             INNER JOIN SETTING S2 ON F1.ID=S1.FEATURE_ID 
             INNER JOIN SETTING S3 ON F1.ID=S1.FEATURE_ID 
             INNER JOIN SETTING S4 ON F1.ID=S1.FEATURE_ID 
             INNER JOIN SETTING S5 ON F1.ID=S1.FEATURE_ID 
   WHERE F1.NAME="network" AND S1.name="IpAddress" AND S1.value="157.184.70.2"
   AND F1.NAME="network" AND S1.name="hostname" AND S1.value="myhostname"
   AND F1.NAME="network" AND S1.name="contact" AND S1.value="mycontact" 
   AND F1.NAME="network" AND S1.name="location" AND S1.value="mylocation"
   AND F1.NAME="print" AND S1.name="pageCount" AND S1.value="1".

This also generates a HUGE amount of rows before it does the WHERE clause, and normally I would limit the self-join using WHERE F1.ID < F2.ID. However, if there is only Feature in the table with a particular name, that excludes it as F1.ID = F2.ID in that case.

So, any ideas?!!!

cheers,

David"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-21 : 09:32:38

so... yeah...

I took a guess at what you where trying to do... (you could have been clearer if you had posted some sample data to work with

)

anyway, this is what I came up with:


Create Table #Printer (Id int not null)
Insert Into #Printer Select 1 Union All Select 2 Union All 
Select 3 Union All Select 4 Union All Select 5

Create Table #Feature (Id int identity(1,1), name varchar(100), Printer_Id int)
Insert Into #Feature
Select A.name, Printer_Id = B.Id 
From (Select Name='Network' Union All Select Name='Print') A
Cross Join #Printer B

Create Table #Setting (Id int identity(1,1), name varchar(100), value varchar(100), Feature_Id int)
Insert Into #Setting
Select A.Name, Value = null, Feature_Id = B.Id
From (
	Select Name='IpAddress' Union All 
	Select Name='HostName' Union All 
	Select Name='Contact' Union All 
	Select Name='Location'
	) A
Cross Join #Feature B
Where B.name = 'Network'
Union All
Select A.Name, Value = null, Feature_Id = B.Id
From 
	(
	Select Name='pageCount'
	) A
Cross Join #Feature B
Where B.name = 'Print'

Update #Setting Set Value = '192.168.0.1' From #Setting Where Id = 1
Update #Setting Set Value = 'myHostName' From #Setting Where Id = 2
Update #Setting Set Value = 'myContact' From #Setting Where Id = 3
Update #Setting Set Value = 'myLocation' From #Setting Where Id = 4
Update #Setting Set Value = '1' From #Setting Where Id = 21

Delete From #Setting Where Feature_Id between 3 and 6

Update #Setting Set Value = '192.168.0.1' From #Setting Where Id = 13
Update #Setting Set Value = 'myHostName' From #Setting Where Id = 14
Delete From #Setting Where Id = 15
Update #Setting Set Value = 'myLocation' From #Setting Where Id = 16
Update #Setting Set Value = '1' From #Setting Where Id = 24

Update #Setting Set Value = '192.168.0.3' From #Setting Where Id = 17
Update #Setting Set Value = 'yourHostName' From #Setting Where Id = 18
Update #Setting Set Value = 'yourContact' From #Setting Where Id = 19
Update #Setting Set Value = 'yourLocation' From #Setting Where Id = 20
Update #Setting Set Value = '1' From #Setting Where Id = 25

--Select * From #Printer
--Select * From #Feature
--Select * From #Setting

	Select Printer=A.Id, MatchCnt = count(*)
	From #Printer A
	Inner Join #Feature B
	On A.Id = B.Printer_Id
	Inner Join #Setting C
	On B.Id = C.Feature_Id
	Where
	(B.name='Network' AND C.name='IpAddress' AND C.value='192.168.0.1') or
	(B.name='Network' AND C.name='HostName' AND C.value='myHostName') or
	(B.name='Network' AND C.name='Contact' AND C.value='myContact') or
	(B.name='Network' AND C.name='Location' AND C.value='myLocation') or
	(B.name='Print' AND C.name='pageCount' AND C.value='1')
	Group By A.Id
	Having count(*)=5 --(# of conditions... comment out to see matches sorted by closeness)
	Order By count(*) desc

Drop Table #Printer
Drop Table #Feature
Drop Table #Setting

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources