Jump to content

sql query


markbett

Recommended Posts

I am strugling with how to properly write this sql query.  I have 2 tables

users and anti_spam

I need to select the email address of all users who have not unsubscribed... antispam has columns id email_address, events, newsletter, invitations, news.... so if a column is marked then the email address in question has unsubscribed so basically its is a query where i say join on email addresses where newsletter = '0'

is that right? Im not too good with complex sql queries...

oh and for clarification there are email addresses that will appear in the anti_spam database table that will not be in the users table..... so essentially i need to get all the email addresses and drop ones where a column has a value of 1 but if there is not match for that column then we assume its 0


wow my explination suxs but i dont know how to explain it
Link to comment
Share on other sites

A bit confusing, but i'll have a go :)

[code]SELECT antispam.email_address
FROM antispam LEFT JOIN users ON antispam.email_address = users.email_address
WHERE newsletter = 0
OR users.email_address IS NULL[/code]

That will give you all email addresses from antispam for which newsletter is 0, OR there is no matching row in users table.  Is that what you mean by "... not match for that column then we assume its 0"?
Link to comment
Share on other sites

thank you for your help but this isn't quiet right

running the query comes back with no results
[code]SELECT anti_spam.as_email
FROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_address
WHERE newsletter = 0
OR users.email_address IS NULL[/code]

this query also doesnt seem correct because i dont need where the users.email doesnt exist.... the problem is that users can exist in the anti spam DB and not exist in the users.... this is so you can say i am not a member and i never want to see anything ever from you...  additionally if you are a member you may not want to be in the anti_spam db so you wont eer have an entry in there
Link to comment
Share on other sites

"example data" meaning you make up some. eg

users_table
[code]
user    email_address
------------------------------
john    john@foo.com
bob    bob@foo.com
[/code]

anti_spam_table
[code]
email_addr        newsletter
------------------------------
john@foo.com  1
....
....
....
[/code]

desired output
[code]
bob
....
....
[/code]

When you're having trouble explaining what you want giving an example usually makes things clearer.

I'll give the query I think you're looking for. If it does not do what you want then give the examples.

[code]
SELECT
u.user
FROM
users AS u
LEFT JOIN
anti_spam AS a
ON
u.email_address = a.email_address
WHERE
a.email_address IS NULL
OR
a.newsletter = 0
[/code]
Link to comment
Share on other sites

[code]thats no different the the first query i tried
{code]SELECT anti_spam.as_email
FROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_address
WHERE newsletter = 0
OR users.email_address IS NULL[/code]

all i need to do is pull all the email addresses where the anti_spam table doesnt have a 1 for the column
Link to comment
Share on other sites

[quote author=markbett link=topic=108710.msg438385#msg438385 date=1158796372]
[code]thats no different the the first query i tried
{code]SELECT anti_spam.as_email
FROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_address
WHERE newsletter = 0
OR users.email_address IS NULL[/code]

all i need to do is pull all the email addresses where the anti_spam table doesnt have a 1 for the column
[/quote]

The 2 queries are not the same. I have to assume you did not try it.

What you're currently asking for doesn't require the users table, but I'm not going to guess if I've interpreted you correctly. To make what you want clear, I've asked you to provide an example in my last 2 posts.

Someone else may be better able to understand without one however.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.