CODEWITHSUNDEEP
mysql
rixter
rixter

Reputation: 1301

Why does this SELECT ... IN statement not work?

I have two MySQL tables in my database, with identical structures, SOURCE and TARGET.

The foreign key I'm using to join the two is the column 'email'.

Basically, TARGET contains only a list of validated emails, which is a subset of the emails in SOURCE, which also contains other address data keyed off of email. I need to pull all the records from SOURCE that have valid emails. Simple, eh? Can't seem to get MySQL to do this...

Here is my query: 

SELECT
  email,
  firstname,
  lastname,
  address,
  city,
  state,
  zip,
  phone
FROM Source 
WHERE email IN
  (SELECT email FROM Target);

When I do this, I get zero records; yet I know that SELECT email FROM Target works, and I know that SOURCE contains the emails.

I'm trying to do this in MySQL Workbench 5.2.34, where the query runs with no errors...but produces no output. (And I know the server is running, etc.)

Upvotes: 0

Views: 83

Answers (2)

rixter
rixter

Reputation: 1301

I actually figured this out...sorry for this red herring of a post: the problem was extra line feeds (\n) characters in the import file used to create the TARGET table. The TRIM function in MySQL does not do anything for that, but this was why the join would not work. Once I removed the extra line feeds in the import file, and reimported the table, voila! Code worked...

Upvotes: 0

What have you tried
What have you tried

Reputation: 11148

Your code works just fine. Without seeing any data it's impossible to say what's causing the issue, but there may be other problems that you're not showing us.

Working fiddle:

http://sqlfiddle.com/#!2/7d505/1

The Schema:

Create table Source(
  email varchar(30),
  firstname varchar(30),
  lastname varchar(30),
  address varchar(30)
);

Create table Target(
  email varchar(30)
);


Insert into Source (email, firstname, lastname, address) VALUES('[email protected]', 'jack', 'smith', '123 pine lane');
Insert into Source (email, firstname, lastname, address) VALUES('[email protected]', 'fake', 'name', '456 pine street');

Insert into Target (email) VALUES('[email protected]');

The Query:

SELECT
  email,
  firstname,
  lastname,
  address
FROM Source 
WHERE email IN
  (SELECT email FROM Target);

The Result:

[email protected]  jack    smith   123 pine lane

Upvotes: 1

Related Questions