Professional Web Applications Themes

Query Required - Microsoft SQL / MS SQL Server

Hello All, Thanks for your previous query feedback, I wanted one more help of such kind SkillValue_ID EmployeeNo SkillType ------------- --------------- ----------- 3 C_0014 3 6 C_0014 1 9 C_0014 2 1 QI_0509 1 2 QI_0509 2 7 QI_0509 1 10 QI_0509 2 12 QI_0509 3 13 QI_0509 3 2 QU_0087 2 3 QU_0087 3 8 QU_0087 1 I wanted a query which returns all EmployeeNo who satisfy the SkillValue_ID condition like this (1 or 10 or 6) AND (2 or 9) AND (3 or 8 or 13) i.e. any of the first criteria AND any of the second criteria ...

  1. #1

    Default Query Required

    Hello All,

    Thanks for your previous query feedback, I wanted one more
    help of such kind


    SkillValue_ID EmployeeNo SkillType
    ------------- --------------- -----------
    3 C_0014 3
    6 C_0014 1
    9 C_0014 2
    1 QI_0509 1
    2 QI_0509 2
    7 QI_0509 1
    10 QI_0509 2
    12 QI_0509 3
    13 QI_0509 3
    2 QU_0087 2
    3 QU_0087 3
    8 QU_0087 1


    I wanted a query which returns all EmployeeNo who satisfy
    the SkillValue_ID condition like this

    (1 or 10 or 6) AND (2 or 9) AND (3 or 8 or 13)



    i.e. any of the first criteria AND any of the second
    criteria AND any of the third criteria

    So the output employeeno in this case should be

    C_0014 and
    QI_0509







    Satish Guest

  2. #2

    Default Query Required

    I have posted the same question, but since it did not
    appear in the thread, I am reposting it.

    Hello All,

    Thanks for your previous query feedback, I wanted one more
    help of such kind


    SkillValue_ID EmployeeNo SkillType
    ------------- --------------- -----------
    3 C_0014 3
    6 C_0014 1
    9 C_0014 2
    1 QI_0509 1
    2 QI_0509 2
    7 QI_0509 1
    10 QI_0509 2
    12 QI_0509 3
    13 QI_0509 3
    2 QU_0087 2
    3 QU_0087 3
    8 QU_0087 1


    I wanted a query which returns all EmployeeNo who satisfy
    the SkillValue_ID condition like this

    (1 or 10 or 6) AND (2 or 9) AND (3 or 8 or 13)



    i.e. any of the first criteria AND any of the second
    criteria AND any of the third criteria

    So the output employeeno in this case should be

    C_0014 and
    QI_0509







    Satish Guest

  3. #3

    Default Re: Query Required

    Try reposting it with DDL and sample data!!!!!!!!!!!1

    --
    Dean Savovic
    www.teched.hr


    "Satish" <com> wrote in message news:071301c34c2a$db63bcf0$gbl... 


    Dean Guest

  4. #4

    Default Re: Query Required

    Here is a slight round-about solution.
    SELECT DISTINCT EmployeeNo FROM testTable WHERE
    EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 1
    OR SkillValue_ID = 10 OR SkillValue_ID = 6)) AND
    EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 2
    OR SkillValue_ID = 9)) AND
    EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 3
    OR SkillValue_ID = 8 OR SkillValue_ID = 13))
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Satish" <com> wrote in message
    news:071301c34c2a$db63bcf0$gbl... 


    SriSamp Guest

  5. #5

    Default Query Required


    You can use

    select employee_id from table where (skillvalue_id in
    (1,10,6)) and (skillvalue_id in(2,9))and (skillvalue_id in
    (3,8,13))


     
    more 
    Arun[Symbiosis] Guest

  6. #6

    Default Re: Query Required

    Try this one:

    create table test
    (
    SkillValue_ID int
    , EmployeeNo char(20)
    , SkillType int
    )

    go

    create function dbo.testfunc (EmployeeNo char(20))
    returns varchar(255)
    as
    begin
    declare ret varchar(255)

    set ret = ','

    select ret = ret + rtrim(convert(char(10), SkillValue_ID)) + ','
    from test
    where EmployeeNo = EmployeeNo

    return ret
    end

    go

    insert into test
    select 3, 'C_0014' , 3
    union
    select 6, 'C_0014' , 1
    union
    select 9, 'C_0014' , 2
    union
    select 1 , 'QI_0509' , 1
    union
    select 2 , 'QI_0509' , 2
    union
    select 7 , 'QI_0509' , 1
    union
    select 10 , 'QI_0509' , 2
    union
    select 12 , 'QI_0509' , 3
    union
    select 13 , 'QI_0509' , 3
    union
    select 2 , 'QU_0087' , 2
    union
    select 3 , 'QU_0087' , 3
    union
    select 8 , 'QU_0087' , 1


    select
    distinct EmployeeNo
    from test
    where (dbo.testfunc(EmployeeNo) like '%' + ',1,' + '%' or dbo.testfunc(EmployeeNo) like '%' + ',10,' + '%' or
    dbo.testfunc(EmployeeNo) like '%' + ',6,' + '%')
    and (dbo.testfunc(EmployeeNo) like '%' + ',2,' + '%' or dbo.testfunc(EmployeeNo) like '%' + ',9,' + '%')
    and (dbo.testfunc(EmployeeNo) like '%' + ',3,' + '%' or dbo.testfunc(EmployeeNo) like '%' + ',8,' + '%' or dbo.testfunc(EmployeeNo)
    like '%' + ',13,' + '%')


    drop table test
    drop function dbo.testfunc



    --
    Dean Savovic
    www.teched.hr


    "Satish" <com> wrote in message news:085801c34c28$aee127a0$gbl... 


    Dean Guest

  7. #7

    Default Re: Query Required

    Your query will not work. Check it out in Query yzer. No rows will be
    returned.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Arun[Symbiosis]" <com> wrote in message
    news:089801c34c2f$0500d3a0$gbl... 
    > more [/ref]


    SriSamp Guest

  8. #8

    Default Query Required

    Thank you all for so much replies. That was great
    response.

    Satish
    satish Guest

  9. #9

    Default Re: Query Required

    Hello Steve,
    Can you explain how your HAVING Clause works. I mean
    the logic behind the usage, nodoubt it works fine.

    Thanks
    Satish

     
    end) + 
    end) + 
    end) [/ref]
    more [/ref]
    satisfy 
    >
    >.
    >[/ref]
    Satish Guest

  10. #10

    Default Re: Query Required

    You can translate Boolean expressions into pure SQL for elaborate
    searches without using dynamic SQL. For example, given a table of job
    candidates and their skills, you might write a query like this:

    skill = Java AND (skill = Perl OR skill = PHP)

    now you need to put it in the disjunctive canonical form. In English,
    for the non-computer science majors, that mean a string of AND-ed groups
    connected by OR operators like this:

    (Java AND Perl) OR (Java AND PHP)

    which we load into this table:

    CREATE TABLE SkillQuery
    (and_grp INTEGER NOT NULL,
    skill CHAR(4) NOT NULL,
    PRIMARY KEY (and_grp, skill));

    INSERT INTO SkillQuery VALUES (1, 'Java');
    INSERT INTO SkillQuery VALUES (1, 'Perl');
    INSERT INTO SkillQuery VALUES (2, 'Java');
    INSERT INTO SkillQuery VALUES (2, 'PHP');

    Assume we have a table of job candidates:

    CREATE TABLE Candidates
    (candidate_name CHAR(10) NOT NULL,
    skill CHAR(4) NOT NULL,
    PRIMARY KEY (candidate_name, skill));

    INSERT INTO Candidates VALUES ('John', 'Java'); --winner
    INSERT INTO Candidates VALUES ('John', 'Perl');
    INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
    INSERT INTO Candidates VALUES ('Mary', 'PHP');
    INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
    INSERT INTO Candidates VALUES ('Larry', 'PHP');
    INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
    INSERT INTO Candidates VALUES ('Moe', 'PHP');
    INSERT INTO Candidates VALUES ('Moe', 'Java');
    INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
    INSERT INTO Candidates VALUES ('Celko', 'Algol');
    INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
    INSERT INTO Candidates VALUES ('Smith', 'Algol');

    The query is simple now:

    SELECT DISTINCT C1.candidate_name
    FROM Candidates AS C1, SkillQuery AS Q1
    WHERE C1.skill = Q1.skill
    GROUP BY Q1.and_grp, C1.candidate_name
    HAVING COUNT(C1.skill)
    = (SELECT COUNT(*) FROM SkillQuery AS Q2 WHERE Q1.and_grp =
    Q2.and_grp);

    You can retain the COUNT() information to rank candidates. For example
    Moe meets both qualifications, while other candidates meet only one of
    the two.

    This query can be generalized for any number of columns. A UNION on
    queries for each of the possible columns will be faster than putting it
    all into a monzster query.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139