Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: What is the best way to omit this value from my results?

    Hi all, first post haha.

    Need some advice:

    I have one table let's say "Loans" that has loan info for customers.

    I have another table "Marketing" that has info for documents that have been mailed to customers. This marketing table has a column called 'offertype' that has a value for each type of letter we mail (e.g, "F", "M", "42")

    This is where my problem is:

    I'm running a query that joins the two tables on (Loan #) which will often give me results such as this --

    (Loan#) (Offertype)

    1001 ------ F
    1001 ------ M
    1001 ------ 42
    1425 ------ F
    1425 ------ 42
    1333 ------ M

    I want to edit the query so that if a (Loan #) has any instance of (Offertype='42'), then that (Loan #) will be kicked out.

    Basically I would want my results to simply be:

    (Loan#) (Offertype)
    1333 ------ M

    I've tried adding a subquery to the Where statement so that (offertype<>'42') but this gives me these results:

    (Loan#) (Offertype)

    1001 ------ F
    1001 ------ M
    1425 ------ F
    1333 ------ M

    Which isn't what I want.

    What would be the best way to omit the loans that have offertype 42? I don't have access to table creation within this particular database.

    Any advice/suggestions would be greatly appreciated

  2. #2
    Join Date
    Apr 2008
    Iasi, Romania
    Provided Answers: 3
    WHERE Loan#
    NOT IN
    (SELECT Loan# From Marketing WHERE OfferTtpe = '42')

    Notice though that NOT IN is a really bad performer. You should search for better ways.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Nov 2004
    Provided Answers: 4
    Try this:
    SELECT Loan#, OfferType
    FROM Marketing
    	LEFT OUTER JOIN (SELECT distinct Loan#
    			From Marketing 
    			WHERE OfferType = '42'
    			) as T ON
    		Marketing.Loan# = T.Loan#
    WHERE T.Loan# = NULL
    Last edited by Wim; 03-01-12 at 15:22.
    With kind regards . . . . . SQL Server 2000/2005/2012

    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

Posting Permissions

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