Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Question Unanswered: Help : Query based on data from multiple rows

    Hello guys

    I am here again with help needed

    this is a sample of what I am trying to do :

    ContractNo ProjectNo WorkNo WoStatus WoType
    11111 2222 1 A x
    11111 2222 2 B Y
    11111 2222 3 B Z
    11111 2222 4 A S
    11111 3333 1 B Y
    11111 3333 2 C Z

    and so on

    I need to select or have a column that gives me only the "ProjectNo"s that got WoStatus 1 and 2and 3 and 4 in WoStatus A or B And WoType X and Y and Z and S

    if this possible ?

    I've read about aggregations but I didn't work our for me

    Thanks very much

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I guess that you mean "...that got WorkNo 1 and 2and 3". It also seems that you're confusing AND and OR logical operators:
    - WorkNo can be 1 OR 2 OR 3
    - WoStatus can be 'A' OR 'B' OR 'C'
    - WoType can be 'X' OR 'Y' OR 'Z'

    If I understand what you're looking for, this query could be the solution (replace "MyTable" by the actual name of the source table):
    Code:
    SELECT MyTable.ProjectNo
    FROM MyTable
    WHERE ((MyTable.WorkNo In (1,2,3,4)) AND 
           (MyTable.WoStatus In ('A','B')) AND
           (MyTable.WoType In ('X','Y','Z')));
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    Thanks Sinndho for your input

    to clarify what I want

    I want to extract the Project number Where :

    1st condition) : It must have at least 4 "WorkNo"
    2nd Condition) : The "WoStatus" must be whether A OR B
    3rd Condition) : Must have ALL the 4 different "WoType" (x,y,z,s)

    Thanks for your help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by georgeb View Post
    Thanks Sinndho for your input

    to clarify what I want

    I want to extract the Project number Where :

    1st condition) : It must have at least 4 "WorkNo"
    2nd Condition) : The "WoStatus" must be whether A OR B
    3rd Condition) : Must have ALL the 4 different "WoType" (x,y,z,s)

    Thanks for your help
    The first condition can be written:
    Code:
    SELECT DISTINCT MyTable.ProjectNo
    FROM MyTable
    WHERE MyTable.ProjectNo In (SELECT ProjectNo 
                                FROM MyTable
                                GROUP BY ProjectNo 
                                HAVING Count(*)>=4 
                               );
    But it is redundant because this condition is implicit from the 3rd Condition: as there can only be one WoType in each row, having four different WoType values implies having at least four rows.

    The second condition is the easiest one and can be written:
    Code:
    SELECT DISTINCT MyTable.ProjectNo
    FROM MyTable
    WHERE (MyTable.WoStatus='a') OR (MyTable.WoStatus='b');
    Or in a more compact expression:
    Code:
    SELECT DISTINCT MyTable.ProjectNo
    FROM MyTable
    WHERE MyTable.WoStatus IN ('a', 'b');
    The third condition can be written (there are other solutions):
    Code:
    SELECT DISTINCT MyTable.ProjectNo
    FROM MyTable
    WHERE (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='x')) AND 
          (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='y')) AND 
          (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='z')) AND 
          (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='S'));
    As we know that the first condition can be eliminated because it is redundant, the final query, which combines the second and third condition, can be written:
    Code:
    SELECT DISTINCT MyTable.ProjectNo
    FROM MyTable
    WHERE ((MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='x')) AND 
           (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='y')) AND 
           (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='z')) AND 
           (MyTable.ProjectNo In (SELECT ProjectNo FROM MyTable WHERE MyTable.WoType='S'))
          ) AND
          (MyTable.WoStatus IN ('a', 'b'));
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    15
    Thank you so much Sinndho, that's very helpful, you saved my job

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Jan 2012
    Posts
    15
    Hi Sinndho

    my manager asked me to change some of the conditions and this is the final result, however I run the query yesterday and I left it till today but still running and no result

    is there is anyway to solve this issue

    (by the way database is not my job)

    Code:
    SELECT DISTINCT WO.SP_NUMBER
    FROM WO
    WHERE ((WO.SP_NUMBER In (SELECT SP_NUMBER FROM WO WHERE WO.CPU_TYPE='Mobiles - RF Safety Compliance')) And (
           (WO.SP_NUMBER In (SELECT SP_NUMBER FROM WO WHERE WO.CPU_TYPE='Mobiles - Detailed Design')) Or 
           (WO.SP_NUMBER In (SELECT SP_NUMBER FROM WO WHERE WO.CPU_TYPE='Mobiles - Preliminary Design')) Or 
           (WO.SP_NUMBER In (SELECT SP_NUMBER FROM WO WHERE WO.CPU_TYPE='Mobiles - SAE')) )
          ) AND
          (WO.WO_STATUS IN ('DLP', 'FC','PW'));

    Thanks very much

Posting Permissions

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