query for 'not exist'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: query for 'not exist'

  1. #1
    Join Date
    Sep 2004
    Posts
    51

    query for 'not exist'

    I have to send out a newsletter for each person in our registration db (fsnep_login) that has newsletter set to 1 (not 0). There is one newsletter that goes out to each person in the registration db that also has a profile in the profile database (fsnep_ha_nutrition). And I think this works correctly for that (correct me please if I am wrong):

    SELECT l.email,n.nutrition_id
    FROM fsnep_ha_nutrition n,fsnep_login l
    WHERE l.newsletter = 1 AND l.login_id = n.login_id

    There is another newsletter for each person in the registration db that is NOT in the profile database. I'm not sure how to do that query. Would I do some sort of sub-query?

    thank you.

    daniel

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Assuming your tables are not too large or you have indexes on login_id columns, this should solve it...

    Code:
    SELECT  l.email
    FROM    fsnep_login l
    WHERE   l.newsletter = 1 
        AND l.login_id not in (select   n.login_id 
                               from     fsnep_ha_nutrition n
                               where    n.login_id = l.login_id)
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2004
    Posts
    51
    It'll be under 2000 records for quite awhile, so I don't think that's a problem.

    I ran these and they looked good, but it seems to me that they didn't count up well.
    I did a count of all registrations where newsletter = 1.
    SELECT l.email
    FROM fsnep_login l
    WHERE l.newsletter = 1
    That came to 903. This should be the total number of the two other queries since they are "all registrations with a profile" and "all registrations without a profile"

    Then I did a the profile query:
    SELECT l.email,n.nutrition_id
    FROM fsnep_ha_nutrition n,fsnep_login l
    WHERE l.newsletter = 1 AND l.login_id = n.login_id
    That came out to 529

    Then I did a no-profile query:
    SELECT l.email
    FROM fsnep_login l
    WHERE l.newsletter = 1
    AND l.login_id not in
    (
    SELECT n.login_id
    FROM fsnep_ha_nutrition n
    WHERE n.login_id = l.login_id
    )
    That came out to 431. Totaling the two queries came up with 960 instead of the 903. If it matters, I also did a query on the number of newsletters <> 1 and that was 98.

    thank you.

    daniel

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Would you mind in checking for duplicates?

    Code:
    select  "LOGIN",
            "QTY"
    from        
    (
    SELECT  login_id "LOGIN",
            count(*) "QTY"
    FROM    fsnep_ha_nutrition
    group by login_id
    )
    where   "QTY" > 1
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Sep 2004
    Posts
    51
    wow, I have 32 instances of duplicates, some with 27 entries. omg. All those add up to 95 extra entries.

    oy, I should figure a script to clean that up.

    okay, then all is probably well.

    thank you.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    glad to help ;-)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width