cross referencing 2 tables and getting results

I have 2 mysql tables

one has a list of user ids that are associated with a city. ie "Fort Lauderdale" but the user id is actually in a column called entity_id and the city is in a field called field_city_value.

This query brings back all of the entity_ids in "Fort Lauderdale"

SELECT entity_id 
FROM  `field_data_field_city` 
WHERE  `field_city_value` LIKE  'Fort Lauderdale'

and then this query brings back the mail for the user id

SELECT mail 
FROM  `users` 
WHERE  `uid` =42

I want to combine the 2 and get all of the mails for al开发者_StackOverflowl of the user ids that match Fort Lauderdale.

Use a join statement.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Heres the long winded way of doing it.. Untested, no mysql access on this box.

SELECT field_data_field_city.entity_id,users.mail FROM users,field_data_field_city WHERE field_city_value LIKE 'Fort Lauderdale' AND field_data_field_city.entity_id = users.uid

Or

SELECT * FROM field_data_field_city city INNER JOIN users user on city.entity_id=user.uid

Can we assume that users.uid is the same as field_data_field_city.entity_id? If that's the case you'll want to look into using MySQL joins

You can try a subselect:

SELECT mail FROM users WHERE uid IN (SELECT entity_id FROM field_data_field_city WHERE field_city_value LIKE 'Fort Lauderdale')

This should do it. You might want to change it a little bit for duplicates or similar, but this simple query should work

SELECT u.mail
FROM users u
   LEFT JOIN field_data_field_city fdfc ON fdfc.entity_id = u.uid
WHERE fdfc.field_city_value LIKE 'Fort Lauderdale'