-
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
-
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.
-
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
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|