| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-16-09, 07:48
|
|
Registered User
|
|
Join Date: Apr 2009
Posts: 1
|
|
|
How to get matching experience in all categegory with min query...
|
|
Can any one help me to get the correct with min query .......
If my table structure like...
Field Type
================================
UserCatId int(11)
UserId int(11)
CategoryId int(11)
Experience int(11)
And; I have to findout the users who have matching experience in all the category mentioned in condition.
i.e.: Any user who have experience in Category Id 1 between exp. between 10 to 15 yrs. As well as CategoryId 2 between experience of 5 and 8 yrs.
and more...
I want to achieve this; could you please suggest me the Query or if any modification required in database Table Structure ?
|
|
04-29-09, 03:05
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 8
|
|
Did you ever figure this out? I'm curious to see what solution you used.
|
|
04-29-09, 03:34
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
Search for relational division
Code:
SELECT userid
FROM da_table
WHERE ( categoryid = 1 AND experience BETWEEN 10 AND 15 )
OR ( categoryid = 2 AND experience BETWEEN 5 AND 8 )
GROUP
BY userid
HAVING Count(*) = 2
|
|
04-29-09, 03:55
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 8
|
|
Here's my problem. There are two tables: activity and person, each containing a different value pair for columns catid1 and catid2. The desired output would be the activity and name columns that satisfy the following condition: a person must have ALL the catid1 and catid2 combinations for a given activity.
If you look at Mike, he satisfies all the requirements for 'running', but not for the others. The output should look like this:
Code:
Mike Running
George Jogging
Marty Cycling
Here's some quick code to create the tables with the data:
Code:
CREATE TABLE [activity] ([activity] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
GO
--
-- [Table] person
--
CREATE TABLE [person] ([name] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
GO
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 1, 1);
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 2, 2);
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 3, 3);
INSERT INTO activity (activity, catid1, catid2) VALUES ('jogging ', 10, 10);
INSERT INTO activity (activity, catid1, catid2) VALUES ('jogging ', 20, 20);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 5, 6);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 6, 7);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 8, 9);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 7, 8);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 10, 10);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 1, 5);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 2, 2);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 3, 3);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 20, 20);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 1, 3);c
INSERT INTO person (name, catid1, catid2) VALUES ('Jimmy ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Jimmy ', 2, 2);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 2, 2);4
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 3, 3);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 45, 45);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 34, 34);
INSERT INTO person (name, catid1, catid2) VALUES ('Billy ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('Joey ', 20, 20);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 1, 5);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 4, 87);
Any ideas? Many many thanks in advance!
|
|
04-29-09, 04:35
|
|
vaguely human
|
|
Join Date: Jun 2007
Location: London
Posts: 2,519
|
|
Quote:
|
Originally Posted by PlatinumRiver
Any ideas?
|
Code:
CREATE TABLE [activity] ([activity] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
GO
--
-- [Table] person
--
CREATE TABLE [person] ([name] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
Your table design don't make any sense to me. Can a person only have 2 activities? What does the field catid2 hold in the activity table? The lengths of your fields are also dangerously short or are you going to ensure that no one will have an activity (or a name) longer than 10 chars?
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to get matching experience in all categegory with min query...
