Create “tblSetting” using following query
/******
Object: Table [dbo].[tblSetting] Script Date: 07/16/2013 11:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblSetting](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UniversityID] [int] NOT NULL,
[CollegeID] [int] NOT NULL,
[cItem] [varchar](50) NOT NULL,
[cDescription] [varchar](200) NOT NULL,
[cValue] [varchar](200) NOT NULL
CONSTRAINT [Pk_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Get record according to College Level or university level or global
level such that we get only one record
Conditions are
1. Get record according to college and University level i.e. where
UniversityID AND CollegeID is non zero
2. Get record according to college level i.e. If 1st
condition does not match then execute this where UniversityID is zero AND
CollegeID is non zero
3. Get record according to University level i.e. If 1st, 2nd
condition does not match then execute this where UniversityID is non zero AND
CollegeID is zero
4. Get record according to global setting level i.e. If 1st,
2nd, 3rd condition does not match then execute this where both UniversityID AND
CollegeID is zero
Get single record by using only one SELECT query.
I follow following way to get the desire result. Execute each
condition one by one and check the result
Condition 4
Insert Following records where UniversityID AND CollegeID is
zero
INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription,
cValue) VALUES(0,0,'FileUploadPath','File Upload Path','\\XYZ\GlobalReports\')
SELECT TOP 1 * FROM tblSetting WHERE
cItem = 'FileUploadPath'
AND
(UniversityID
= 177 OR
UniversityID = 0)
AND (CollegeID = 65 OR CollegeID = 0) ORDER BY CollegeID DESC , UniversityID DESC
Here our 1st, 2nd, 3rd conditions are not satisfied, however 4th
condition is valid. So its output is
|
ID
|
UniversityID
|
CollegeID
|
cItem
|
cDescription
|
cValue
|
|
1
|
0
|
0
|
FileUploadPath
|
File Upload Path
|
\\XYZ\GlobalReports\
|
Condition 3
Insert Following records where UniversityID is non zero AND
CollegeID is zero
INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription,
cValue) VALUES(177,0,'FileUploadPath','File Upload Path','\\XYZ\UnivReports\')
SELECT TOP 1 * FROM tblSetting WHERE
cItem = 'FileUploadPath'
AND
(UniversityID
= 177 OR
UniversityID = 0)
AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC
Here 1st, 2nd, 4rd conditions are not satisfied, however 3rd
condition is valid. So its output is
|
ID
|
UniversityID
|
CollegeID
|
cItem
|
cDescription
|
cValue
|
|
2
|
177
|
0
|
FileUploadPath
|
File Upload Path
|
\\XYZ\UnivReports\
|
Condition 2
Insert Following records where UniversityID is zero AND
CollegeID is non zero
INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription,
cValue) VALUES(0,65,'FileUploadPath','File Upload Path','\\XYZ\CollegeReports\')
SELECT TOP 1 * FROM tblSetting WHERE
cItem = 'FileUploadPath'
AND
(UniversityID
= 177 OR
UniversityID = 0)
AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC
Here our 1st, 3rd, 4th conditions are not satisfied, however 2th
condition is valid. So its output is
|
ID
|
UniversityID
|
CollegeID
|
cItem
|
cDescription
|
cValue
|
|
3
|
0
|
65
|
FileUploadPath
|
File Upload Path
|
\\XYZ\CollegeReports\
|
Condition 4
Insert Following records where both UniversityID AND
CollegeID is non zero
INSERT INTO dbo.tblSetting (UniversityID, CollegeID, cItem, cDescription,
cValue) VALUES(177,65,'FileUploadPath','File Upload Path','\\XYZ\UnivCollegeReports\')
SELECT TOP 1 * FROM tblSetting WHERE
cItem = 'FileUploadPath'
AND
(UniversityID
= 177 OR
UniversityID = 0)
AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC
Here our 1st, 2nd, 3rd conditions are not satisfied, however 4th
condition is valid. So its output is
|
ID
|
UniversityID
|
CollegeID
|
cItem
|
cDescription
|
cValue
|
|
4
|
177
|
65
|
FileUploadPath
|
File Upload Path
|
\\XYZ\UnivCollegeReports\
|
As you can analyze we are using same SELECT query for all
output
SELECT TOP 1 * FROM tblSetting WHERE
cItem = 'FileUploadPath'
AND
(UniversityID
= 177 OR
UniversityID = 0)
AND (CollegeID = 65 OR CollegeID = 0)ORDER BY CollegeID DESC , UniversityID DESC
I believe there are multiple ways of doing this in SQL Server.
Let me know if you have any other way of achieving the desire result.
Please leave your comments / suggestion.
