Jump to content


Photo

sql query


  • Please log in to reply
8 replies to this topic

#1 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 20 September 2006 - 01:21 AM

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

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 20 September 2006 - 03:29 AM

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

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

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

#3 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 20 September 2006 - 07:08 PM

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

running the query comes back with no results
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

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


#4 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 20 September 2006 - 09:37 PM

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

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


#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 September 2006 - 09:46 PM

Post example data and desired output. Be thorough.

#6 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 20 September 2006 - 09:53 PM

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

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 September 2006 - 10:24 PM

"example data" meaning you make up some. eg

users_table
user    email_address
------------------------------
john    john@foo.com
bob     bob@foo.com

anti_spam_table
email_addr        newsletter
------------------------------
john@foo.com   1
....
....
....

desired output
bob
....
....

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.

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


#8 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 20 September 2006 - 11:52 PM

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

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

#9 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 21 September 2006 - 02:24 AM

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

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


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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users