Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2011
    Posts
    4

    Unanswered: Multiple match criteria

    Given the table example below, I need a query to return all the people who have taken both parts of a course. (From this data, I'd only want Joe and Fred in the results.)

    What's the best way? I'm drawing a blank. Thanks!

    Code:
    Name     Course
    Joe      Intro Part 1
    Joe      Intro Part 2
    Sue      Intro Part 1
    Fred     Adv Part 1
    Fred     Adv Part 2
    Mary     Adv Part 2

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT Name
    FROM DaTable
    GROUP BY Name
    HAVING COUNT(*) = 2
    Please let me know what mark your teacher gave you for "your" solution

    Will do homework for a beer.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2011
    Posts
    4
    Not valid - students could take part 1 more than once (which was not shown in the example.) Many more fields will be needed in the final query, which prohibits grouping. Not all courses have 2 parts, and there could also be more than 2 parts in future courses.

    I'll ignore the ridiculous comment in hopes that a better suggestion will come forward.

  4. #4
    Join Date
    Aug 2011
    Posts
    4

    Thumbs down

    I have been reading this forum for a while, and I see reappearing the same names over and over again of a little group of active forum members who are willing to help others with their problems. We all know their names. Over time I have learned a lot of them and I really appreciate their efforts.

    Then I read this thread, started by jefferbob. Please do correct me when my analysis is wrong somewhere:
    - He is incapable of solving his own problem.
    Quote Originally Posted by jefferbob View Post
    Not valid - students could take part 1 more than once (which was not shown in the example.) Many more fields will be needed in the final query, which prohibits grouping. Not all courses have 2 parts, and there could also be more than 2 parts in future courses.
    - He is incapable of giving a decent description of his problem, changing the rules when a valid solution for the problem description and examples he provided is given.
    I'll ignore the ridiculous comment in hopes that a better suggestion will come forward.
    - He ridicules/insults the only person trying to help him out (ever heard of tongue-in-cheek?) And yes, it was also my impression that this is a typical school assignment. I have the solution in my school book. But you will first have to give more information. Even I could give you the solution then, but I'm not keen on receiving one of your blunt "Not valid" reactions as a thank you for my effort.
    - He is unwilling to come up with something himself, preferring others to do the work for him.

    Two thumbs down on jefferbob.

    Jefferbob, you came to this forum with a problem. The least you can do is to be polite and grateful for what others are voluntary willing to do for you.

  5. #5
    Join Date
    Aug 2011
    Posts
    4
    Please allow me to correct your analysis:

    Quote Originally Posted by Cupatee View Post
    - He is incapable of solving his own problem. - He is incapable of giving a decent description of his problem, changing the rules when a valid solution for the problem description and examples he provided is given.
    The example that was provided covered the vast majority of records in question. In trying to keep the question simple, I apologize for not covering every possible combination of data, and I should have presented all the additional criteria for potential situations. That was certainly my fault. However, your assumption of my being incapable of solving the problem is incorrect. I already have several possible solutions for the proc, as it is a rather large multi-table multi-database multi-system (SQL Server and Oracle) situation. Being a Microsoft Certified Trainer, as well as a Microsoft Certified Solution Developer, I spent 13 years training programmers and providing consulting nationwide, in environments where multiple possible solutions were presented and ripped apart to determine the best course of action.

    Quote Originally Posted by Cupatee View Post
    - He ridicules/insults the only person trying to help him out (ever heard of tongue-in-cheek?) And yes, it was also my impression that this is a typical school assignment. I have the solution in my school book. But you will first have to give more information. Even I could give you the solution then, but I'm not keen on receiving one of your blunt "Not valid" reactions as a thank you for my effort.
    In past and current work environments, I am used to coworkers providing helpful suggestions and constructive criticism, not time-wasting comments based solely on assumptions. If I had know that this site required empty platitudes, I wouldn't have bothered anyone in the first place.

    Quote Originally Posted by Cupatee View Post
    - He is unwilling to come up with something himself, preferring others to do the work for him.
    I think this assumption has already been clarified.

    For any new readers, please disregard the question in the original post, as this has been a huge waste of time.

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Given your new information: It is impossible to solve your problem, as the courses and number of parts in each course is not known.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    Aug 2011
    Posts
    4
    Allow me to give you my impressions on your reply.
    Quote Originally Posted by jefferbob View Post
    The example that was provided covered the vast majority of records in question. In trying to keep the question simple, I apologize for not covering every possible combination of data, and I should have presented all the additional criteria for potential situations.
    That is a totally acceptable explanation. Why didn't you wrote that and included those other examples and criteria in your first reply?

    Quote Originally Posted by jefferbob
    However, your assumption of my being incapable of solving the problem is incorrect. I already have several possible solutions for the proc, as it is a rather large multi-table multi-database multi-system (SQL Server and Oracle) situation.
    Why didn't you post those and asked whatever further advice you needed? People on this forum are often asked to provide proof that they too are willing to invest some time in a solution for their problem. It would have created a lot of goodwill.

    Quote Originally Posted by jefferbob
    Being a Microsoft Certified Trainer, as well as a Microsoft Certified Solution Developer, I spent 13 years training programmers and providing consulting nationwide, in environments where multiple possible solutions were presented and ripped apart to determine the best course of action.
    One could expect that a person with so much experience, would be able to explain his problem in a clear and correct way. You have not done that so far.

    Quote Originally Posted by jefferbob
    In past and current work environments, I am used to coworkers providing helpful suggestions and constructive criticism
    The solution that was given to you was a perfect solution for the problem you had posted. If a perfect answer is not a helpful suggestion, I don't know what is.
    You blame him for making the assumption that your problem was from a school assignment. Yet on the other hand you expected him to assume that you would misrepresent your problem. And that his solution would not only have to give those students who have followed both course parts, but also those with a single course and the future students who would have taken all three courses or all four or .... Why is making assumptions required in one case and plain wrong in another case?

    Quote Originally Posted by jefferbob
    not time-wasting comments based solely on assumptions. If I had know that this site required empty platitudes, I wouldn't have bothered anyone in the first place.
    I like the few jokes that are made sometimes on this forum, but YMMV.

    Quote Originally Posted by jefferbob
    - He is unwilling to come up with something himself, preferring others to do the work for him.
    I think this assumption has already been clarified.
    Not at all. You have not shown a single line of code of those "I already have several possible solutions for the proc".

    Quote Originally Posted by jefferbob
    For any new readers, please disregard the question in the original post, as this has been a huge waste of time.
    So you conclude that when someone tries to help you with a problem that you have misrepresented, he is wasting your time, and not you wasting his time?

    Now let's have a look at your first post that started this thread:
    Quote Originally Posted by jefferbob
    Given the table example below, I need a query to return all the people who have taken both parts of a course. (From this data, I'd only want Joe and Fred in the results.)

    What's the best way? I'm drawing a blank. Thanks!

    Code:
    Name     Course
    Joe      Intro Part 1
    Joe      Intro Part 2
    Sue      Intro Part 1
    Fred     Adv Part 1
    Fred     Adv Part 2
    Mary     Adv Part 2
    "I'm drawing a blank." Is this a question from a Microsoft Certified large multi-table multi-database multi-system training and consulting guy who "I already have several possible solutions for the proc"?

    Quote Originally Posted by jefferbob View Post
    In past and current work environments, I am used to coworkers providing helpful suggestions and constructive criticism
    I see you have only submitted a total of 3 posts on this forum, so they must all come from this single thread. For someone who has a claimed past of 13 years of training and consulting nationwide, you have left very little "helpful suggestions" for others.
    I wonder if your current and past co-workers are used to you providing them with helpful suggestions and constructive criticism, or more of the kind of feedback you gave on this thread? Like first asking them a wrong question and then blaming them for wasting your time.

  8. #8
    Join Date
    Aug 2011
    Posts
    4
    I'm bowing to your amazing powers of assumption, and this will be my last post on the subject. The only clarification I will make is that the comment about this being a waste of time was applicable to everyone participating in this thread - not just the selfish bastard you assume that i am. Feel free to continue to waste as much of your time as you choose, as I am making an assumption that you will have to get in the last word.

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Jefferbob: I think it's about to cool down. I responded yesterday, stating that it is impossible to solve your problem. You have not presented any further information making it possible, so perhaps it is time to be a bit more humble here, and admit that incorrect and insufficient information is given, and present enough information, not just complaining about others.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I appreciate Cupatee's intervention and I agree with him/her.
    I didn't respond myself, as I always try to refrain myself from participating in flame wars as they normally lead to nothing of any value.
    Quote Originally Posted by roac View Post
    I think it's about to cool down.
    These are the wisest words on this thread. I'll try to cool down myself.

    Cupatee's mention that this problem was in his course book, made me remember where I had seen it: "find all pilots that can fly all the planes present in the hangar". Add "Celkco" when you Google for it. I should have remembered, it is one of the SQL questions for the selection of new developers here.

    Jefferbob, I didn't like your reaction, but I hope you have sufficient information to solve your problem yourself now. If not, I'm sure others will be willing to help you further.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Aug 2011
    Posts
    4
    Quote Originally Posted by Wim View Post
    "find all pilots that can fly all the planes present in the hangar". Add "Celkco" when you Google for it. I should have remembered, it is one of the SQL questions for the selection of new developers here.
    Do you mean that a solicitant who can not solve that question, will never get a job in your company?

  12. #12
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Even if one could solve all the SQL problems, he/she is not sure about getting hired. Nor is failing all of them a guarantee of not getting hired.

    Fortunately no solicitant was ever able to solve all the SQL problems, so we can see how they react. Some get frustrated and give up, others react angry, some block completely, others persist and are able to solve them with some help. Their SQL knowledge at one moment in time, hampered by stress, is not very important and can be learned on the job. Much more important is their character: are they people we can see ourselves working with for the coming years.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  13. #13
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Or as I said once: A good consultant is not one that knows everything, but one who can solve virtually everything. Good problem solving techniques, knowing where and how to look for addition information is utterly important, especially under stress.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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