RESOLVED: Asking for help with a sql logic problem

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)

RESOLVED: Asking for help with a sql logic problem

Author

Topic

TonyN
Starting Member

4 Posts

Posted - 2006-01-09 : 17:54:10

Problem:
I would like to have a query or set of queries, that when passed a MallClientID, returns each ServerID that has at least one matching softwareid for each softwaretype.
At somepoint, I will probably also want to pass in the ServerID and return each MallClientID that have at least one matching softwareid for each softwaretype. :-)

Upfront disclaimers:
1) I tried to search the forums for existing examples or related code, I am posting because I either suck at searching or there are no posts that compare to my problem.
2) I have a little experience with SQL, but most of my experience is not with Operation Sets, but rather with Loops, aka C/C#.
3) Being new, I appreciate the ability to have access to this forum and the people that have worked hard to support it.
4) After seeing the problem, if you can recommend a book that I can read that would give me the knowledge to solve the solution on my own, that would be greatly appreciated as well.
5) After looking at this problem, I am hoping/expecting for someone to tell me how easy this is to solve, and I will call myself a bonehead!
6) After looking at this post, do I need a better subject?

Tables & Relationships:
MallClient
MallClientID Identity int
MallClientName nvcarchar(255) (Unique Index Constraint)

MallClientSoftware
MallClientID (FK to MallClient)
SoftwareID (FK to Software)
* - Both of these create the primary key

Server
ServerID Identity int
ServerName nvcarchar(255) (Unique Index Constraint)

ServerSoftware
ServerID (FK to Server)
SoftwareID (FK to Software)
* - Both of these create the primary key

Software
SoftwareID Identity int
SoftwareName nvcarchar(255) (Unique Index Constraint)
SoftwareTypeID int (FK back to SoftwareType)

SoftwareType
SoftwareTypeID Identity int
SoftwareType nvcarchar(255) (Unique Index Constraint)

Records:
MallClient
ID ~ ClientName
1 ~ Client1
2 ~ Client2
3 ~ Client3

MallClientSoftware
MallClientID ~ SoftwareID
1 ~ 1
1 ~ 3
2 ~ 1
3 ~ 5

Server
ID ~ ServerName
1 ~ Server1
2 ~ Server2
3 ~ Server3

ServerSoftware
ServerID ~ SoftwareID
1 ~ 1
1 ~ 3
1 ~ 4
2 ~ 1
2 ~ 4
3 ~ 6

Software
ID ~ SoftwareName ~ SoftwareTypeID
1 ~ Windows XP Pro ~ 1
2 ~ Windows 2000 Pro ~ 1
3 ~ Firefox 1.5 ~ 2
4 ~ Internet Explorer 6.0 SP1 ~ 2
5 ~ Opera 6.0
6 ~ Netscape Navigator 7.1

SoftwareType
SoftwareTypeID ~ SoftwareType
1 ~ Operating System
2 ~ Web Browser

So for example:
If I pass Client1, I get in return Server1 and Server2.
If I pass Client2, I get in return Server1 and Server2.
If I pass Client3, I get nothing in return.

I have started down the path with the following query:

select count(*)
from (select
ss.SoftwareID
,s.SoftwareTypeID
from ServerSoftware ss
join Software s on ss.SoftwareID = s.SoftwareID
where not exists (select distinct s2.SoftwareTypeID
from MallClientSoftware mcs
join Software s2 on mcs.SoftwareID = s2.SoftwareID
where mcs.ExecutionClientID = 3 and s2.SoftwareTypeID = s.SoftwareTypeID)) MyQuery

This will show a count of Software records returning a set of softwaretypes that DONT exist between the relation of Client and Server, which can invalidate which servers WONT work with the given client, but then I still have a bunch of more logic checks. Like, if the client and server then share the same software types, do they then share the same softwareids for each type. (And thats where I really need some learnin/help, because "for each" is what I am used to and not Operation Sets. I have read this resource along with a couple of books I bought for the definition of Operation Set: http://www.eps-software.com/download/whitepaper_tsql.pdf

Well, I hope I can find some help from you all.

For those of you that have read all of this, thank you for your time! I know its precious!.

Tony

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-09 : 19:20:54

Hi Tony,
I know you went to a lot of trouble to post all that but there is something you could do to make it MUCH easier on us. Generate the script to create the tables and write insert statements for you sample data. Then we'd have everything ready to provide a solution that we know works. check out this link for example of what I mean.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

TonyN
Starting Member

4 Posts

Posted - 2006-01-09 : 20:15:39

After reading your resource, thank you for that, here is the code that should build the tables and populate the data.

Thanks,
Tony

I wrote this, then copied this code 3 seperate times into Query Analyzer (Sql 2000) each time against a new database I called Test, to test the code.

/*
************************************************************************************************************************
** Create tables
************************************************************************************************************************
*/
-- Create MallClient table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MallClient]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MallClient]
GO

CREATE TABLE [dbo].[MallClient] (
	[MallClientID] [int] IDENTITY (1, 1) NOT NULL ,
	[MallClientName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL	
) ON [PRIMARY]
GO

-- Create MallClientSoftware table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MallClientSoftware]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MallClientSoftware]
GO

CREATE TABLE [dbo].[MallClientSoftware] (
	[MallClientID] [int] NOT NULL ,
	[SoftwareID] [int] NOT NULL 	
) ON [PRIMARY]
GO

-- Create Server table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Server]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Server]
GO

CREATE TABLE [dbo].[Server] (
	[ServerID] [int] IDENTITY (1, 1) NOT NULL ,
	[ServerName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL	
) ON [PRIMARY]
GO

-- Create ServerSoftware table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ServerSoftware]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ServerSoftware]
GO

CREATE TABLE [dbo].[ServerSoftware] (
	[ServerID] [int] NOT NULL ,
	[SoftwareID] [int] NOT NULL 	
) ON [PRIMARY]
GO

-- Create Software table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Software]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Software]
GO

CREATE TABLE [dbo].[Software] (
	[SoftwareID] [int] IDENTITY (1, 1) NOT NULL ,
	[SoftwareTypeID] [int] NOT NULL ,
	[SoftwareName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL	
) ON [PRIMARY]
GO

-- Create SoftwareType table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SoftwareType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SoftwareType]
GO

CREATE TABLE [dbo].[SoftwareType] (
	[SoftwareTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[SoftwareType] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL	
) ON [PRIMARY]
GO

/*
************************************************************************************************************************
** Add primary keys to tables above
************************************************************************************************************************
*/
-- MallClient Primary Key
ALTER TABLE [dbo].[MallClient] WITH NOCHECK ADD 
	CONSTRAINT [PK__MallClient] PRIMARY KEY  NONCLUSTERED 
	(
		[MallClientID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO

-- MallClientSoftware Primary Key
ALTER TABLE [dbo].[MallClientSoftware] WITH NOCHECK ADD 
	CONSTRAINT [PK__MallClientSoftware] PRIMARY KEY  NONCLUSTERED 
	(
		[MallClientID] ,
		[SoftwareID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO

-- Server Primary Key
ALTER TABLE [dbo].[Server] WITH NOCHECK ADD 
	CONSTRAINT [PK__Server] PRIMARY KEY  NONCLUSTERED 
	(
		[ServerID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO

-- ServerSoftware Primary Key
ALTER TABLE [dbo].[ServerSoftware] WITH NOCHECK ADD 
	CONSTRAINT [PK__ServerSoftwaree] PRIMARY KEY  NONCLUSTERED 
	(
		[ServerID] ,
		[SoftwareID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO
-- Software Primary Key
ALTER TABLE [dbo].[Software] WITH NOCHECK ADD 
	CONSTRAINT [PK__Software] PRIMARY KEY  NONCLUSTERED 
	(
		[SoftwareID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO

-- SoftwareType Primary Key
ALTER TABLE [dbo].[SoftwareType] WITH NOCHECK ADD 
	CONSTRAINT [PK__SoftwareType] PRIMARY KEY  NONCLUSTERED 
	(
		[SoftwareTypeID]
	) WITH  FILLFACTOR = 80  ON [PRIMARY] 
GO


/*
************************************************************************************************************************
** Add foreign keys to tables above
************************************************************************************************************************
*/

-- MallClient Foreign Key
	-- NO KEYS
	
-- MallClientSoftware Foreign Key
if exists (select * from sysobjects where name = 'FK__MallClientSoftware__MallClient')
	alter table [dbo].[MallClientSoftware] drop constraint [FK__MallClientSoftware__MallClient] 
GO
alter table [dbo].[MallClientSoftware] add constraint [FK__MallClientSoftware__MallClient] 
	foreign key ([MallClientID]) 
	references [dbo].[MallClient]([MallClientID]) not for replication
GO


if exists (select * from sysobjects where name = 'FK__MallClientSoftware__Software')
	alter table [dbo].[MallClientSoftware] drop constraint [FK__MallClientSoftware__Software] 
GO
alter table [dbo].[MallClientSoftware] add constraint [FK__MallClientSoftware__Software] 
	foreign key ([SoftwareID]) 
	references [dbo].[Software]([SoftwareID]) not for replication
GO

-- Server Foreign Key
	-- NO KEYS
	
-- ServerSoftware Foreign Key
if exists (select * from sysobjects where name = 'FK__ServerSoftware__Server')
	alter table [dbo].[ServerSoftware] drop constraint [FK__ServerSoftware__Server] 
GO
alter table [dbo].[ServerSoftware] add constraint [FK__ServerSoftware__Server] 
	foreign key ([ServerID]) 
	references [dbo].[Server]([ServerID]) not for replication
GO


if exists (select * from sysobjects where name = 'FK__ServerSoftware__Software')
	alter table [dbo].[ServerSoftware] drop constraint [FK__ServerSoftware__Software] 
GO
alter table [dbo].[ServerSoftware] add constraint [FK__ServerSoftware__Software] 
	foreign key ([SoftwareID]) 
	references [dbo].[Software]([SoftwareID]) not for replication
GO

-- Software Foreign Key
if exists (select * from sysobjects where name = 'FK__Software__SoftwareType')
	alter table [dbo].[Software] drop constraint [FK__Software__SoftwareType] 
GO
alter table [dbo].[Software] add constraint [FK__Software__SoftwareType] 
	foreign key ([SoftwareTypeID]) 
	references [dbo].[SoftwareType]([SoftwareTypeID]) not for replication
GO

-- SoftwareType Foreign Key
	-- NO KEYS
	
/*
************************************************************************************************************************
** Add indexes to tables above
************************************************************************************************************************
*/

-- MallClient Index
CREATE UNIQUE INDEX [XAK1MallClient] ON [dbo].[MallClient]([MallClientName]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

-- MallClientSoftware Index
	-- NO INDEXES
-- Server Index
CREATE UNIQUE INDEX [XAK1Server] ON [dbo].[Server]([ServerName]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
-- ServerSoftware Index
	-- NO INDEXES
-- Software Index
CREATE UNIQUE INDEX [XAK1Software] ON [dbo].[Software]([SoftwareTypeID],[SoftwareName]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
-- SoftwareType Index
CREATE UNIQUE INDEX [XAK1SoftwareType] ON [dbo].[SoftwareType]([SoftwareType]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

/*
************************************************************************************************************************
** Add data to tables above
************************************************************************************************************************
*/
insert into SoftwareType (SoftwareType)
values ('Operating System')
-- select @TestSuiteID = @@IDENTITY --if you want to see the identity of the softwaretype record that was just created

insert into SoftwareType (SoftwareType)
values ('Web Browser')

insert into Software (SoftwareTypeID, SoftwareName)
values (1, 'Windows XP Pro')

insert into Software (SoftwareTypeID, SoftwareName)
values (1, 'Windows 2000 Pro')

insert into Software (SoftwareTypeID, SoftwareName)
values (2, 'Firefox 1.5')

insert into Software (SoftwareTypeID, SoftwareName)
values (2, 'Internet Explorer 6.0 SP1')

insert into Software (SoftwareTypeID, SoftwareName)
values (2, 'Opera 6.0')

insert into Software (SoftwareTypeID, SoftwareName)
values (2, 'Netscape Navigator 7.1')

insert into MallClient (MallClientName)
values ('Client1')

insert into MallClientSoftware (MallClientID, SoftwareID)
values (1, 1)

insert into MallClientSoftware (MallClientID, SoftwareID)
values (1, 3)

insert into MallClient (MallClientName)
values ('Client2')

insert into MallClientSoftware (MallClientID, SoftwareID)
values (2, 1)

insert into MallClient (MallClientName)
values ('Client3')

insert into MallClientSoftware (MallClientID, SoftwareID)
values (3, 5)

insert into Server (ServerName)
values ('Server1')

insert into ServerSoftware (ServerID, SoftwareID)
values (1, 1)

insert into ServerSoftware (ServerID, SoftwareID)
values (1, 3)

insert into ServerSoftware (ServerID, SoftwareID)
values (1, 4)

insert into Server (ServerName)
values ('Server2')

insert into ServerSoftware (ServerID, SoftwareID)
values (2, 1)

insert into ServerSoftware (ServerID, SoftwareID)
values (2, 4)

insert into Server (ServerName)
values ('Server3')

insert into ServerSoftware (ServerID, SoftwareID)
values (3, 6)

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-09 : 23:13:42

Hi Tony,

quote:

So for example:
If I pass Client1, I get in return Server1 and Server2.
If I pass Client2, I get in return Server1 and Server2.
If I pass Client3, I get nothing in return.

I think this is what you are looking for
select distinct d.servername
from MallClient a
inner join MallClientSoftware b
on b.MallClientid = a.MallClientid
inner join
ServerSoftware c
on b.softwareid = c.softwareid
inner join
Server d
on c.serverid= d.serverid
where a.mallclientname = 'Client1'

TonyN
Starting Member

4 Posts

Posted - 2006-01-10 : 02:17:16

shallu1,
That seems to be a good start, though if I add the following data, the query doesnt satisfy the part where I would like to only return a server match that has at least one softwareid match for each softwaretype back to the client:

insert into SoftwareType (SoftwareType)
values ('Graphics Editor')

insert into Software (SoftwareTypeID, SoftwareName)
values (3, 'Adobe Photoshop 8.0')

insert into ServerSoftware (ServerID, SoftwareID)
values (1, 7)

The above data creates a new software type with one new software record with that type and changing the Server1 Software to include the new Software.

So when I rerun your query I am still returned Server1 for Client1 and Client2 when those clients do not have any records relating to SoftwareTypeID 3.

seeing how you did the inner joins, I will be reading up to see if I need to add another join (inner?) for the softwaretype.

Thanks for the help so far, now I have some data to work with.

-Tony

TonyN
Starting Member

4 Posts

Posted - 2006-01-12 : 19:12:35

First off - I did not have the appropriate expected results when I first defined my problem.

What I really wanted was this :
If I pass Client1, I get in return Server1 and Server2.
If I pass Client2, I get nothing in return.
If I pass Client3, I get nothing in return.

Client2 was NOT to get either server because it did not share a matching
software for SoftwareType 2 that both Server1 and Server2 had records
for.

So looking further into the appropriate operation set solution, and with some great help from another friend with SQL skills, I have the following query that looks to resolve my issue:

-- getservers
declare @MallClientID int
select @MallClientID = 1

select serv.ServerID
      ,serv.ServerName
from Server serv 
where exists (select count(distinct ss.SoftwareTypeID)
              from ServerSoftware ssw
              join Software ss on ssw.SoftwareID = ss.SoftwareID
              join MallClientSoftware mcs on ss.SoftwareID = mcs.SoftwareID
                                             and mcs.MallClientID = @MallClientID
              where ssw.ServerID = serv.ServerID
              having count(distinct ss.SoftwareTypeID) >= (select count(distinct s.SoftwareTypeID)
                                                           from ServerSoftware ss2
                                                           join Software s on ss2.SoftwareID = s.SoftwareID
                                                           where ss2.ServerID = serv.ServerID) and count(distinct ss.SoftwareTypeID) > 0)

I hope this potentially helps others with similiar issues.

Thanks for those who took the time looking at this problem.

-Tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources