MySQL JOIN query -

Question

I know this question has already asked here, but I can't figure this one out from the previous answers.

We've got 2 tables:

members
-----------------------
| id | country_iso_3 |
-----------------------
  1    USA
  2    DZA
  3    FRA
  4    ILI
  5    USA
  6    USA


members_details
-----------------------
| member_id | city    |
-----------------------
  1          AA
  2          BB
  3          CC
  4          DD
  5          EE
  6          FF

Now I want to query members_details and select the cities which are from the same countries, here "AA", "EE" and "FF" should be the results (because the members are from USA)

I know how to compare different cols from different tables, but here we need to check the second table 'member_id' and the first table 'id (country_iso_3)' somehow!

andy · Accepted Answer · 2012-11-03 09:03:23Z

1

Select city from members LEFT JOIN members_details 
ON members.id = members_details.memberid Where country_iso_3 = 'USA'

answered Nov 3, 2012 at 9:03

andy

6,0792 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahmoud Gamal · Accepted Answer · 2012-11-03 09:06:33Z

1

Just JOIN the two tables, with a WHERE clause for the country you want to get the city of it like so:

SELECT md.city 
FROM members m
INNER JOIN members_details md ON m.id = md.memberid
WHERE m.country_iso_3 = 'USA'

SQL Fiddle Demo

edited Nov 3, 2012 at 9:06

answered Nov 3, 2012 at 9:01

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Comments

mvp · Accepted Answer · 2012-11-03 09:25:30Z

0

This should work, and not only for USA, but for any country which is listed more than once:

SELECT d.city
FROM members m,
     members_details d
WHERE d.member_id = m.id
  AND m.country_iso_3 IN (
    SELECT country_iso_3
    FROM members
    GROUP BY 1
    HAVING count(1) > 1
)

answered Nov 3, 2012 at 9:25

mvp

118k15 gold badges132 silver badges155 bronze badges

Collectives™ on Stack Overflow

MySQL JOIN query -

3 Answers 3

Comments

SQL Fiddle Demo

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related