LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices



Reply
 
Search this Thread
Old 05-12-2008, 06:14 AM   #1
rookiepaul
Member
 
Registered: Jul 2005
Location: London
Distribution: Debian, RedHat, Ubuntu
Posts: 73

Rep: Reputation: 15
MySQL - Query and compare 2 tables


Hi Guys,

I have two tables in a mysql database: Subscribers and Unsubscribers. Subscribers is a table containing the details of everyone who signs up for our newsletters (email address, name etc). When people sign up for our website they're details are placed in subscribers. When people unsubscribe they're details go into unsubscribers. It's a stupid system. If I have built the system I would have had a binary unsubscribed field in the subscriber table set to one if they choose to unsubscribe. But what this means is that when I want a list of all currently subscribed people, I need to compare the two tables and pull out the details of all the people who appear in the subscribers table but not the unsubscribers table. This is where I fall down. I'm pretty good with standard queries but I've never compared 2. If I was able to use PHP I would write a loop to do this but I have to do this at the command line so I'm stuck. Can anyone help. Much appreciated.
 
Old 05-12-2008, 06:50 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,415

Rep: Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968
a nested statement is probably the simplest logic to use, not the most elegant, but for a one off...

Code:
select username from subscribers users where username not in (select username from unsubscribers)
alternatively a left join would be more technically correct...

Code:
select subscribers.username from subscribers left join unsubscribers where unsubscribers.username is null

Last edited by acid_kewpie; 05-12-2008 at 06:52 AM.
 
Old 05-12-2008, 07:31 AM   #3
rookiepaul
Member
 
Registered: Jul 2005
Location: London
Distribution: Debian, RedHat, Ubuntu
Posts: 73

Original Poster
Rep: Reputation: 15
They didn't seem to work. I'm not sure I was clear on what I needed.

I need the EmailAddress of all the users in Subscribers who's NotifyServices field = 1 and who's EmailAddress is NOT in the Unsubscribers table. Sorry for the confusion.

Many thanks.
 
Old 05-12-2008, 07:44 AM   #4
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,415

Rep: Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968Reputation: 1968
OK, well it's only an example, i'm sure you can add in those extra clauses...


select emailaddress from subscribers users where username not in (select username from unsubscribers) and NotifyServices = 1
 
Old 05-12-2008, 10:05 AM   #5
rookiepaul
Member
 
Registered: Jul 2005
Location: London
Distribution: Debian, RedHat, Ubuntu
Posts: 73

Original Poster
Rep: Reputation: 15
Ok, I finally got it working, but the query is a bit different to the one that you suggested.

Code:
SELECT Subscribers.EmailAddress, Subscribers.NotifyServices, Subscribers.SubscriberID FROM AlertSystem.Subscribers Subscribers WHERE (Subscribers.NotifyServices = 1) AND (Subscribers.SubscriberID NOT IN (SELECT DISTINCT CancelledSubscribers.SubscriberID FROM AlertSystem.CancelledSubscribers CancelledSubscribers));
Thanks for the help.

Paul.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
interesting MySQL query/view query :s mjh Programming 3 03-25-2008 08:30 AM
help with mysql query: return nth rows in query hawarden Programming 2 07-31-2006 07:36 PM
Mysql Compare two tables from different databases baddah Programming 4 04-20-2006 01:36 AM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 10:45 AM
SQL query, comparing tables ngomong Programming 3 07-07-2002 08:44 PM


All times are GMT -5. The time now is 01:19 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration