Results 1 to 7 of 7

Thread: Where !=

  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: Where !=

    Hi, I am having trouble trying to figure out how to make this query work, I'm sure by looking at it, you'll be able to see what I'm trying to accomplish...
    Code:
    PROCEDURE dbo.GetAvailableCertificationsByEmployeeID
    	(
    	@EmployeeID int
    	)
    AS
    	SET NOCOUNT ON;
    	
    	DECLARE @CertificationID int
    
    SELECT @CertificationID = CertificationID FROM EmployeeCertifications WHERE EmployeeID = @EmployeeID ;
    
    SELECT CertificationName FROM Certifications WHERE CertificationID != @CertificationID;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT CertificationName 
    FROM Certifications 
    WHERE NOT EXISTS    (SELECT NULL
                FROM    FROM EmployeeCertifications 
                WHERE     EmployeeID = @EmployeeID
                    AND EmployeeCertifications.CertificationID = Certifications.CertificationID)
    Love the proc name - I like big, long, totally unambiguous names like that. Better a few extra key strokes than wonder "WTF does that proc actually do?"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, not really. but then, am assuming that a person can have several certifications (MSCE, Oracle Expert, Cisco, A+, etc.). Your procedure will query the EmployeeCertifications table, and store the last result returned (which can be different from run to run, depending on various conditions) in a variable. Then you go to the Certifications table, and get a list of CertificationNames that do not equal the single one you plucked at random from the EmployeeCertifications table. Was that the requirement?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Reads the proc name! Read the proc name!
    A proc that needs no introduction. He barely even needed to say "Hi".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Posts
    3
    Quote Originally Posted by pootle flump
    Love the proc name - I like big, long, totally unambiguous names like that. Better a few extra key strokes than wonder "WTF does that proc actually do?"
    Thank you, I like to use very descriptive proc names and variables and such, that way I don't have to wonder "What the hell was this for?" later if I come back to it...

    And thank you very much, the query worked like a charm...
    Last edited by WebWired; 01-28-09 at 12:31.

  6. #6
    Join Date
    Jan 2009
    Posts
    3
    Quote Originally Posted by MCrowley
    Well, not really. but then, am assuming that a person can have several certifications (MSCE, Oracle Expert, Cisco, A+, etc.). Your procedure will query the EmployeeCertifications table, and store the last result returned (which can be different from run to run, depending on various conditions) in a variable. Then you go to the Certifications table, and get a list of CertificationNames that do not equal the single one you plucked at random from the EmployeeCertifications table. Was that the requirement?
    No, the goal is to populate a ListBox with ALL the Certifications that the Employee DOES NOT possess...

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    SELECT CertificationName 
    FROM Certifications 
    WHERE CertificationID not in (SELECT CertificationID 
    		FROM EmployeeCertifications 
    		WHERE EmployeeID = @EmployeeID)

Posting Permissions

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