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

    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
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    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
    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

Posting Permissions

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