Getting specific values. - 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 2005 Forums

Transact-SQL (2005)

Getting specific values.

Author

Topic

SDK
Starting Member

3 Posts

Posted - 2008-06-10 : 07:45:46

Hi Everyone.

Looong story short. I have a screen with search criteria. Right.

I have table :
tblCandidateLicense
intPkCandidateLicenseID (PK)
intFkCandidateId
intFkLicenseTypeID

And Another table
tblLicenseTypeDef
Columns:
intPkLicenseTypeID (Pk Int)
strDescription
strLicenseCode
intRank (just for ranking)
intRankParent (for grouping types of vehicles)

tblLicenseTypeDef data contains:

1 , MotorCycle < 125,0,1,0
2 , MotorCycle > 125,0,2,0
3 , Car 1 ,0,0,1
4 , Car 2 ,0,1,1
5 , Car 3 ,0,2,1
5 , Car 4 ,0,3,1

tblCandidateLicense data contains

1 , 57, 1
2 , 57, 3
3 , 58, 4

I use the intRank to determine which vehicle is bigger and intRankParent to make groups (so motorbikes and cars searched don't bring back the wrong type)

I want to be able to search on License Type .

But now a person can search for car 4 and motorcyle > 125. Then my query needs to find all the people with car type 4 AND Smaller (car-1-2-3-4) (that is group 4), and motorcycle and smaller (1 and 2) (Group 0)

If a person search for car type Car 3 and motorcycle < 125, then car 1-2-3 and MotorCycle 1 must be returned.

I hope it makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 07:56:41

One method is by using a temp table

INSERT INTO #Temp
SELECT * FROm tblLicenseTypeDef t
WHERE @Description  LIKE t.strDescription + '%'

INSERT INTO #Temp
SELECT t.* FROm tblLicenseTypeDef t
INNER JOIN #Temp tmp
ON tmp.intRankParent=t.intRankParent
AND tmp.intRank<t.intRank

select * from #temp will give all the results

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 08:00:56

[code]-- Prepare sample data
DECLARE @LicenseTypeDef TABLE
(
intPkLicenseTypeID INT,
strDescription VARCHAR(200),
strLicenseCode VARCHAR(200),
intRank INT,
intRankParent INT
)

INSERT @LicenseTypeDef
SELECT 1, 'MotorCycle < 125', 0, 1, 0 UNION ALL
SELECT 2, 'MotorCycle > 125', 0, 2, 0 UNION ALL
SELECT 3, 'Car 1' , 0, 0, 1 UNION ALL
SELECT 4, 'Car 2' , 0, 1, 1 UNION ALL
SELECT 5, 'Car 3' , 0, 2, 1 UNION ALL
SELECT 6, 'Car 4' , 0, 3, 1

DECLARE @CandidateLicense TABLE
(
intPkCandidateLicenseID INT,
intFkCandidateID INT,
intFkLicenseTypeID INT
)

INSERT @CandidateLicense
SELECT 1, 57, 1 UNION ALL
SELECT 2, 57, 3 UNION ALL
SELECT 3, 58, 4 UNION ALL
SELECT 4, 99, 2 UNION ALL
SELECT 5, 99, 5

-- Show the expected output
SELECT cl.intFkCandidateID,
ltd.strDescription
FROM @CandidateLicense AS cl
INNER JOIN @LicenseTypeDef AS x ON x.intPkLicenseTypeID = cl.intFkLicenseTypeID
INNER JOIN @LicenseTypeDef AS ltd ON ltd.intRankParent = x.intRankParent
AND ltd.intRank >= x.intRank
ORDER BY cl.intFkCandidateID,
x.intRankParent,
x.intRank[/code]

E 12°55'05.25"
N 56°04'39.16"

SDK
Starting Member

3 Posts

Posted - 2008-06-10 : 08:16:24

thanks for the quick replies guys. I will give it a whirl!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 08:20:03

Or this:-

DECLARE @LicenceDef table
(intPkLicenseTypeID  Int,
strDescription varchar(20),
strLicenseCode  int,
intRank int,
intRankParent int)
INSERT INTO @LicenceDef
SELECT 1 , 'MotorCycle < 125',0,1,0
UNION ALL
SELECT 2 , 'MotorCycle > 125',0,2,0
UNION ALL
SELECT 3 , 'Car 1' ,0,0,1
UNION ALL
SELECT 4 , 'Car 2' ,0,1,1
UNION ALL
SELECT 5 , 'Car 3' ,0,2,1
UNION ALL
SELECT 5 , 'Car 4' ,0,3,1
DECLARE @Search varchar(8000)--your serach string
SET @Search='Car 3,MotorCycle > 125'--test value


;
With CTE (ID,Descn,Code,[Rank],Parent) AS
(
SELECT * FROM @LicenceDef WHERE ','+@Search+',' LIKE '%,' + strDescription + ',%'

UNION ALL

SELECT l.* FROM @LicenceDef l
INNER JOIN CTE c on c.Parent=l.intRankParent
AND c.[Rank]-1=l.intRank
)
select * from CTE ORDER BY ID
ID          Descn                Code        Rank        Parent
----------- -------------------- ----------- ----------- -----------
1           MotorCycle < 125     0           1           0
2           MotorCycle > 125     0           2           0
3           Car 1                0           0           1
4           Car 2                0           1           1
5           Car 3                0           2           1

SDK
Starting Member

3 Posts

Posted - 2008-06-10 : 09:33:40

Peso..Just a quick question

I want to pass the intPkLicenseTypeID in (example 2,5 [MotorCycle > 125 and Car 3] , I want everyone that has (4,3) and (2,1) back. (Each intRankParent group and intRank less than what was passed in)

Does it make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources