Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

select varchar fields in table1 that are not in table2

Posted on 2013-11-16
2
Medium Priority
?
387 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
 
LVL 1

Author Closing Comment

by:rgb192
ID: 39654968
thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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
Course of the Month13 days, 10 hours left to enroll

580 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