Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

select varchar fields in table1 that are not in table2

Posted on 2013-11-16
2
Medium Priority
?
386 Views
Last Modified: 2013-11-17
CREATE TABLE `saved_searches` (
  `savedSearchesID` bigint(20) unsigned NOT NULL auto_increment,
  `savedSearchesDefaultUsername` varchar(50) default NULL,
  PRIMARY KEY  (`savedSearchesID`)
);

 CREATE TABLE `a_messages` (
  `a_messages_id` int(11) NOT NULL auto_increment,
  `profile_id` varchar(20) default NULL,
  `message_id` bigint(20) default NULL,
  `this_user` varchar(20) default NULL,
  PRIMARY KEY  (`a_messages_id`),
  UNIQUE KEY `unique_message_id` (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=utf8;

saved_searches.savedSearchesDefaultUsername is the same as a_messages.profile_id
want to select all the
saved_searches.savedSearchesDefaultUsername
that are not yet in
a_messages.profile_id

sample data
saved_searches.savedSearchesDefaultUsername
bob
sarah
susan

 a_messages.profile_id
bob

so only select
sarah and susan
0
Comment
Question by:rgb192
2 Comments
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 2000 total points
ID: 39654389
Hello rgb192,

You can write your query as following

SELECT saved_searches.savedSearchesDefaultUsername
FROM saved_searches
WHERE NOT
EXISTS (

SELECT 1
FROM a_messages
WHERE a_messages.profile_id = saved_searches.savedSearchesDefaultUsername
)

Open in new window


Hope, this will help you.

Thank you.

Amar Bardoli wala
0
 

Author Closing Comment

by:rgb192
ID: 39654968
thanks
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question