0

I am trying to run a query off of my mysql database using the IN operator.

SELECT usernum FROM usergroup WHERE emailmain IN ('blah@blah.com', 'rawr@rawr.com', 'e@e.com')

The query works but instead of outputing the usernum values in the order entered it outputs them in descending order. This is problematic because I need them to be aligned to the emails. I think it may have to do with the limit operator or by using an ORDER BY emailmain. I've tried that and it still doesn't work because assuming the email doesn't exist in the database it won't correspond to the memory location in the array.

The most important thing to me is that I know the emails that aren't in the database.

Any help would be appreciated!

Tadeck
  • 132,510
  • 28
  • 152
  • 198
Trent
  • 87
  • 8
  • 1
    Why would you expect them in any order if you do not specify one? It may help to use the "EXPLAIN SELECT ..." syntax to decided on which index the database is using to return your data. If you do not specify one - mysql will use the index that it decided was best to answer your query – Adrian Cornish Jan 05 '12 at 04:50
  • Yea i'm not sure what I was thinking. Its late and I didn't think about indexes. However, I am still running into the issue of it not telling me which emails aren't in the database. – Trent Jan 05 '12 at 05:03
  • 1
    this could be your problem - you are thinking like I do - which is as a programmer - not DBA - 99.9999% of all emails are not in you db - you need to think "set theory" on what you want - a rough guess is "this list of emails is not in my DB" My suggestion is to create a table of 'said' email and then do a left join and check the NULLs. – Adrian Cornish Jan 05 '12 at 05:06
  • 1
    That is a really good idea Adrian. I wish I didn't have to create a table each time but that will work. I really appreciate the help. I wish I could give you some more rep. Thanks again! – Trent Jan 05 '12 at 05:13
  • 1
    if I got paid by rep I'd care :-) but hopefully you've worked out a good solution for your self – Adrian Cornish Jan 05 '12 at 05:20
  • [Take a look on this link][1]. It will be helpful [1]: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause – Fionaa Miller Jan 05 '12 at 05:38

5 Answers5

1

You can use ORDER BY FIELD to define your order

SELECT usernum 
FROM usergroup 
WHERE emailmain IN ('blah@blah.com', 'rawr@rawr.com', 'e@e.com')
ORDER BY FIELD (emailmain, 'blah@blah.com', 'rawr@rawr.com', 'e@e.com')
JohnP
  • 49,507
  • 13
  • 108
  • 140
  • I tried this but it wouldn't show me the emails not in the database. Thank you for the help though! – Trent Jan 05 '12 at 04:59
  • This isn't meant to show you the emails in the database. This will only order the emails in the exact order that you specify. – JohnP Jan 05 '12 at 05:13
  • This didn't solve the issue but it will definitely be useful in the future! Thank you! – Trent Jan 05 '12 at 05:14
1

IN won't impact the order of your results at all, without an ORDER clause rows will come back in whatever order they're found in.

If you need to match the order of the results to the order of your supplied email list, try this:

SELECT usernum
  FROM usergroup
  WHERE emailmain IN ('blah@blah.com', 'rawr@rawr.com', 'e@e.com')
  ORDER BY FIND_IN_SET(emailmain, "blah@blah.com,rawr@rawr.com,e@e.com")

FIND_IN_SET will return the position of the given string in a comma-delimited list

With larger lists it would probably be safer though to just use SELECT usernum, emailmain FROM ... to return both fields in each result and correlate them application-side.

The Mighty Chris
  • 1,578
  • 13
  • 17
  • 1
    I didn't know about the [FIELD](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field) function JohnP posted while I was working on my answer. It's probably a better choice than FIND_IN_SET – The Mighty Chris Jan 05 '12 at 04:57
  • Neither did I. It will be helpful in the future. However, the main reason why its important I get it sorted correctly is so that I know which emails are not in the database. After executing i'm using mysql_fetch_assoc to get the values and then set them equal to an array. However, it won't show the emails that aren't in the database. – Trent Jan 05 '12 at 05:06
  • Maybe what you want is `SELECT usernum FROM usergroup WHERE emailmain NOT IN ('blah@blah.com', 'rawr@rawr.com', 'e@e.com')` – The Mighty Chris Jan 05 '12 at 18:46
1

If you are creating the IN string dynamically, you don't have to create a table every time you run the query - you can use an inline view, populated in the same way as the IN string, like so:

select v.email, u.usernum
from 
(select 'blah@blah.com' email union select 'rawr@rawr.com' union select 'e@e.com') v
left join usergroup u on v.email = u.emailmain
order by v.email
  • I had some issues with this at first but it works perfectly by just changing the collation in phpmyadmin to latin1_swedish_ci. I really appreciate the help. Thanks! – Trent Jan 05 '12 at 17:13
0

any reason why you're not just selecting the username and email from the database?

SELECT usernum, emailmain FROM usergroup WHERE emailmain IN ('blah@blah.com', 'rawr@rawr.com', 'e@e.com') ORDER BY emailmain DESC
uberweb
  • 325
  • 1
  • 4
  • 12
0

Providing IN condition does not imply the order.

To order your result, use ORDER BY at the end of your query.

Tadeck
  • 132,510
  • 28
  • 152
  • 198