query include "primary" and "secondary constraint

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

SQL Server Development (2000)

query include "primary" and "secondary constraint

Author

Topic

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 03:39:36

Dear all,

I need to select some members from DB.

The primary constraint is that must be followed the requirement.
The seconardary constrait is that it is prefered but not necessary.

The constaint include Sex, Age, hobby, cities, etc

I have think that I should first gett the result set from primary constaint query, then order it by secondary constaint. However, when the secondary is cities(eg CityA, CityB, CityC is prefered).

1. How to construct the order query?
2. Any way to measure the "degree of preference" of secondary constraint?
Therefore I can sort it in the output result set

Thx

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 04:02:50

Give table structure, sample and result data
Read this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 04:52:18

Thank for your help!

More clear explanation is as follow

My Requirement
(I) Primary Constrain: must be satisfy
1. JobCat columen must be 1
2. Lastname column must either be ('lastname1',' lastname2', 'lastname3')

(II) secondary constrain:
If rows match any one following options
A) in city: New York, WDC
B) hobby: playing tennis
add the row " One" mark

At the end, I want the result order by the mark calculated

What I know to do now is :

select * from TestMemberTable
where JobCat = 1 .......
(or how to use the "order by " in my case)

Then how to construct the remaing query?

Thanks!!

/*

CREATE TABLE [dbo].[TestMemberTable] (
[LoginName] [varchar] (50) NOT NULL,
[Last Name] [varchar] (50) null,
[Gender] [char] (1) NULL ,
[City] [varchar] (50) NULL ,
[JobCat] [int] null,
[Hobby] [varchar] (100) Null
)

Insert into [dbo].[TestMemberTable] values
('login1', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing football')
Insert into [dbo].[TestMemberTable] values
('login2', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing volleyballs')
Insert into [dbo].[TestMemberTable] values
('login4', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing football')
Insert into [dbo].[TestMemberTable] values
('login6', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing tennis')
Insert into [dbo].[TestMemberTable] values
('login7', 'lastname3', 'M', 'Hong Kong', '1', 'Reading, sleeping, playing tennis')
Insert into [dbo].[TestMemberTable] values
('login5', 'lastname5', 'F', 'Hong Kong', '1', 'Writing Novels, swiming, playing football')
Insert into [dbo].[TestMemberTable] values
('login3', 'lastname3', 'F', 'Beijing', '1', 'Reading, swiming, playing football')
*/

be a hardworking people!!

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 04:55:22

Result set by order

('login6', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing tennis') mark = 2

('login1', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing football') mark = 1

('login2', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing volleyballs') mark = 1

('login4', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing football') mark = 1

('login7', 'lastname3', 'M', 'Hong Kong', '1', 'Reading, sleeping, playing tennis') mark = 1

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 05:03:58

What do you mean by mark?

See if this runs

select * from TestMemberTable
where jobcat=1 and City in ('New York','WDC')
and hobby like '%playing tennis%' and [last name] in ('lastname1','lastname2','lastname3')

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 05:16:05

For the primary constraint, the following query satisfy my primary request
select * from TestMemberTable
where jobcat=1 [last name] in ('lastname1','lastname2','lastname3')

Then For the seconardary constaint, it can exist "or" not exist
A) in city: New York, WDC
B) hobby: playing tennis

If the row satisfy more constraints stated above, it should rank higher in the final result set.

To achieve this, my orginal idea is that I can give a score for each constraint in same rows. By adding all score in each row, then i can compare the score of all row and use "order by" to order the result set

Then, How should I do this?

be a hardworking people!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-10 : 05:56:06

Try this. If it does not work, post expected result from your sample data


select * from TestMemberTable
where jobcat=1 and [last name] in ('lastname1','lastname2','lastname3')
Union all
select * from TestMemberTable
where jobcat=1 and [last name] in ('lastname1','lastname2','lastname3') and
City in ('New York','WDC') 
and hobby like '%playing tennis%'

Madhivanan

Failing to plan is Planning to fail

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 07:05:55

1. the result value should be distinct.

2. The order of final result set is important since my client first used the top row member first.

3. In order to sort out the most suitable member. My client provide the secondary requirement which is most prefer but not necessary

My orginal thought
Orgianlly, I think i can use a math formula to calculate relative importance of all row(that all have already fullfill the primary constraint). Then, the row can be sort by just "order by" in query based on calculated value.

In my simple case, only 2 secondary requriement in it, however, there should be more criteria.

Expected Result
(Pls see below)
For the "Login6", all primary and secondary requirement is fullfilled. For "Login7", which only pass the primary constraint

To sum up, how to integrate a relative importance formalas so that the result are show by relative importance to my client

Thanks you your kind help!
I will improve my comm skills!!!

/************Input data*********************/
Insert into [dbo].[TestMemberTable] values
('login1', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing football')
Insert into [dbo].[TestMemberTable] values
('login6', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing tennis')
Insert into [dbo].[TestMemberTable] values
('login7', 'lastname3', 'M', 'Hong Kong', '1', 'Reading, sleeping, playing piano')
Insert into [dbo].[TestMemberTable] values
('login5', 'lastname5', 'F', 'Hong Kong', '1', 'Writing Novels, swiming, playing football')

/****************Expected output**************/
select * from....
Result Set
('login6', 'lastname2', 'M', 'WDC', '1', 'Reading, sleeping, playing tennis')
('login1', 'lastname1', 'M', 'New York', '1', 'Reading, swiming, playing football')
('login7', 'lastname3', 'M', 'Hong Kong', '1', 'Reading, sleeping, playing piano')

/********************/

be a hardworking people!!

acdacd
Yak Posting Veteran

63 Posts

Posted - 2005-08-10 : 13:45:22

madhivanan,
Thx for your help before. Can you give me some follow-up suggestion?

Can anyone help me in this problem?

be a hardworking people!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources