markbett Posted September 20, 2006 Share Posted September 20, 2006 I am strugling with how to properly write this sql query. I have 2 tablesusers and anti_spamI 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 0wow my explination suxs but i dont know how to explain it Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/ Share on other sites More sharing options...
btherl Posted September 20, 2006 Share Posted September 20, 2006 A bit confusing, but i'll have a go :)[code]SELECT antispam.email_addressFROM antispam LEFT JOIN users ON antispam.email_address = users.email_addressWHERE newsletter = 0OR 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"? Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95061 Share on other sites More sharing options...
markbett Posted September 20, 2006 Author Share Posted September 20, 2006 thank you for your help but this isn't quiet rightrunning the query comes back with no results[code]SELECT anti_spam.as_emailFROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_addressWHERE newsletter = 0OR 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 Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95623 Share on other sites More sharing options...
markbett Posted September 20, 2006 Author Share Posted September 20, 2006 i've tried a bunch of queries and im still not getting what i need...[code]SELECT anti_spam.as_emailFROM anti_spamLEFT JOIN users ON anti_spam.as_email = users.email_addressWHERE newsletter =0OR newsletter IS NULL [/code] Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95689 Share on other sites More sharing options...
shoz Posted September 20, 2006 Share Posted September 20, 2006 Post example data and desired output. Be thorough. Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95694 Share on other sites More sharing options...
markbett Posted September 20, 2006 Author Share Posted September 20, 2006 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... Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95703 Share on other sites More sharing options...
shoz Posted September 20, 2006 Share Posted September 20, 2006 "example data" meaning you make up some. egusers_table[code]user email_address------------------------------john john@foo.combob 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]SELECTu.userFROMusers AS uLEFT JOINanti_spam AS aONu.email_address = a.email_addressWHEREa.email_address IS NULLORa.newsletter = 0[/code] Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95716 Share on other sites More sharing options...
markbett Posted September 20, 2006 Author Share Posted September 20, 2006 [code]thats no different the the first query i tried{code]SELECT anti_spam.as_emailFROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_addressWHERE newsletter = 0OR 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 Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95744 Share on other sites More sharing options...
shoz Posted September 21, 2006 Share Posted September 21, 2006 [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_emailFROM anti_spam LEFT JOIN users ON anti_spam.as_email = users.email_addressWHERE newsletter = 0OR 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. Quote Link to comment https://forums.phpfreaks.com/topic/21342-sql-query/#findComment-95822 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.