0

I believe this should be relatively simple but I can't seem to figure out the logic to it.

I'll start with what I'm looking for. I wanted to be able to pass in values (such as e-mail addresses) through a SELECT query and have it output whether those rows exist or not. It would be something like this:

# PASS Values test@test.com and example@example.com in.
# For these purposes let's pretend that test@test.com exists
# and example@example.com does not.

+----------------------+--------+
| E-mail               | Exists |
+----------------------+--------+
| test@test.com        | 1      |
| example@example.com  | 0      |
+----------------------+--------+

There is the answer here for a single result: MySQL EXISTS return 1 or 0 but I would like to expand on that and am having issues figuring it out.

Any help would be great. Thanks!

Community
  • 1
  • 1
MillerMedia
  • 3,651
  • 17
  • 71
  • 150

1 Answers1

1

Assuming I'm understanding your question correctly, you can't do this with in alone. One option is to create a subquery with an outer join, and then use case:

select t.email, 
       case when yt.email is not null then 1 else 0 end `exists`
from (select 'test@test.com' as email union all select 'example@example.com') t
    left join yourtable yt on t.email = yt.email

Note: backticks are needed on exists in the statement.

MillerMedia
  • 3,651
  • 17
  • 71
  • 150
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • This makes sense. It is throwing an error that I'm trying to identify. The error is: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists,from (select 'test@test.com' as email union all select 'example@example. at line 2 `. Another question, I would be able to put as many e-mails as I'd like onto that union all select statement, correct? – MillerMedia Jul 04 '16 at 23:48
  • I tried removing the `union all select` statement to just test the one e-mail address but that also threw an error. It seems to be a syntax error... – MillerMedia Jul 04 '16 at 23:51
  • @MillerMedia Remove the comma after 'exists' word – Mostafa Vatanpour Jul 05 '16 at 02:19
  • I removed the commas and still nothing. The error message is: https://www.dropbox.com/s/vcbpzsnv6ic5ga3/Screenshot%202016-07-05%2015.02.16.png?dl=0 . It still seems to be indicating a syntax error. It's erroring around 'test@test.' ...could the period be causing some issue? – MillerMedia Jul 05 '16 at 22:03
  • @MillerMedia -- look at your `select` statement. You should have a comma after `t.email`, but not after `exists`: `select field1, field2 from...`. Also be aware that `exists` is a keyword and may need to be escaped with backticks. – sgeddes Jul 05 '16 at 22:37
  • Yes, I did leave the comma after t.email and it still gave the same error. Let me try to backtick `exists` and I'll let you know what I get. – MillerMedia Jul 05 '16 at 22:58
  • That was it. exists needs backticks on it. Thanks, very helpful! – MillerMedia Jul 05 '16 at 22:58
  • Question (and apologies; I'm trying to wrap my mind around the concept), I'm looking to expand the query to scale to have more than just two inputs. I know I can't just add an extra e-mail to the union all select statement because then it wouldn't have as many fields as the select statement itself. But if I just add an arbitrary field to the select statement then the join doesn't pick all of the arguments up. I'm probably overthinking it, but is there a quick way to just scale the query to be able to scale the query to check more e-mails? – MillerMedia Jul 06 '16 at 06:24