Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: query help select and disinct

    Still trying to get a grasp on big complicated queries.. here is the one I am working on now.

    Works great except I want to to grab distinct contactID's currently it returns duplicate contactID cause mulitple CID's exsist in the history table.

    Code:
    SELECT DISTINCT TOP 100 PERCENT dbo.History.ContactID AS Expr1, dbo.Contacts.SOLD, dbo.History.Subject, dbo.Contacts.Address, dbo.Contacts.Email
    FROM         dbo.History INNER JOIN
                          dbo.Contacts ON dbo.History.ContactID = dbo.Contacts.ContactID
    GROUP BY dbo.History.ContactID, dbo.Contacts.SOLD, dbo.History.Subject, dbo.Contacts.Address, dbo.Contacts.Email
    HAVING      (NOT (dbo.History.Subject LIKE '%5th wheel show%')) AND (dbo.Contacts.Address IS NOT NULL) AND (dbo.Contacts.Email IS NOT NULL) OR
                          (NOT (dbo.History.Subject = '2003 Travel Trailer Blowout Mailer Sent')) OR
                          (NOT (dbo.History.Subject LIKE '%2003 diesel%')) OR
                          (NOT (dbo.History.Subject LIKE '%2003 bounder Sale%'))
    ORDER BY dbo.Contacts.SOLD DESC
    thanks a bunch

    D--
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: query help select and disinct

    You are looking for contacts with a specific history? Specify it that way!

    1) Specify your history
    SELECT distinct ContactID from History WHERE subject like <WhatEver>

    2) Join this with your contact information
    SELECT DISTINCT TOP 100 PERCENT C.ContactID, C.SOLD, C.Address, C.Email
    FROM dbo.History AS C INNER JOIN (SELECT distinct ContactID from History WHERE subject like <WhatEver>) AS H ON H.ContactID = C.ContactID

    Simple, isn't it? You miss your history subject, because when you have multiple history records per contact, you have multiple subjects, which you would have to accumulate on contact level. Consider to use Min(), Max() or Count(*).
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is unnecessary to use a subquery for this problem, and it is undesirable to use one that employs the DISTINCT clause, because you scrap any index you have on the field.

    SELECT DISTINCT TOP 100 PERCENT
    dbo.Contacts.ContactID,
    dbo.Contacts.SOLD,
    dbo.History.Subject,
    dbo.Contacts.Address,
    dbo.Contacts.Email
    FROM dbo.Contacts
    INNER JOIN dbo.History on dbo.Contacts.ContactID = dbo.History.ContactID
    WHERE dbo.Contacts.Address IS NOT NULL
    AND dbo.Contacts.Email IS NOT NULL
    OR dbo.History.Subject NOT LIKE '%5th wheel show%'
    OR dbo.History.Subject NOT LIKE '2003 Travel Trailer Blowout Mailer Sent'
    OR dbo.History.Subject NOT LIKE '%2003 diesel%'
    OR dbo.History.Subject NOT LIKE '%2003 bounder Sale%'

    ghornet,

    The above code is greatly simplifed from what you submitted, but it will continue to give you duplicate CIDS as long as more than one subject can be returned from the History table. You need to be more specific with your criteria if you want to wheedle this down to a single record, or use DoktorBlue's suggestion of an aggregate query with a Min or Max function (can't believe this is what you want though, because you'd never be sure what records where and where not being returned across CIDS). I'd think a criteria such as "Most recent history record", or "Highest priority issue" would be more appropriate.

    Several things you should notice from the above code:
    1) Your OR statements are at the same criteria level as your NOT NULL criteria, so contacts with null addresses or emails will still be returned if they have a history subject that is not excluded.
    2) Your OR statements are all on the same criteria level, so that even a subject such as 'abc2003 dieseldef' would end up being included since it is not like '%5th wheel show%'.
    3) The use of an INNER JOIN means that contacts with no associated history records will not show up in the output.

    Long story short, you need to do a lot more thinking about your WHERE clause.

    blindman

  4. #4
    Join Date
    Jul 2003
    Posts
    23

    What I did

    Code:
    SELECT DISTINCT 
                          dbo.Contacts.Salutation, dbo.Contacts.FirstName, dbo.Contacts.LastName, dbo.Contacts.Address, dbo.Contacts.City, dbo.Contacts.State, 
                          dbo.Contacts.Zip, dbo.Contacts.ContactID, dbo.Contacts.Email
    FROM         dbo.Contacts LEFT OUTER JOIN
                          dbo.History ON dbo.Contacts.ContactID = dbo.History.ContactID
    WHERE     (NOT (dbo.History.Subject LIKE '%5th wheel show%')) AND (NOT (dbo.History.Subject = '2003 Travel Trailer Blowout Mailer Sent')) AND 
                          (NOT (dbo.History.Subject LIKE '%2003 Diesel%')) AND (NOT (dbo.History.Subject LIKE '%2003 bounder Sale%')) AND 
                          (dbo.Contacts.Address IS NOT NULL) AND (dbo.Contacts.City IS NOT NULL) AND (dbo.Contacts.State IS NOT NULL) AND 
                          (dbo.Contacts.Email IS NOT NULL)
    This seemed to work the way I wanted it to... I was tired yesterday when I was posting, much easier this morning.. thanks for the input


    D--
    "Everything is possible, somethings are just less likely then others"

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your criteria logic at least looks consistent now, but I'd encourage you to clean up the code so it reads easier. You'll be less likely to make a mistake in the future when you have to modify it.

    So now you are just using your history table as a filter for your contact table? In a cases like this you may want to use a NOT EXISTS clause instead of a join, and then you could ditch the DISTINCT selection.

    blindman

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I didn't checked every detail, but I have the following comments:

    1) your LEFT OUTER JOIN becomes an INNER JOIN when you add conditions on the outer join table in the WHERE clause

    2) Your DISTINCT is cleaning your mess, which you got because you are multiplying your contacts in the first place. That is why I placed my DISTINCT in the subquery.

    3) If your do not need any history info in your seet list, take bm's advice and turn it into an EXIST condition.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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