sql query

[markbett]

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

[btherl]

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"?

[markbett]

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

[markbett]

i've tried a bunch of queries and im still not getting what i need...

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

[shoz]

Post example data and desired output. Be thorough.

[markbett]

example data?  well there isnt any... when i run that query i come back with NO results...  there are users in the USERS table and nothing is stored inthe anti_spam table so i want all the users to be returned...

[shoz]

"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]

[markbett]

[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

[shoz]

[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.