Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: 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 ?

  2. #2
    Join Date
    Oct 2003
    Posts
    8
    Did you ever figure this out? I'm curious to see what solution you used.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  4. #4
    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!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •