SQL JOIN Query - Confused

I'm working 开发者_如何学JAVAon a currency conversion service with a friend, but have came accross a big problem, near the end of the project.

We have 3 tables, currencys (Contains currency code e.g. 'GBP', Currency name, current rate and timestamp), countries (Contains country code e.g. 'US' and the location it refers to), currency_country (Contains country code and currency code, these are composite primary key e.g. A sample entry would be like so: FR - EUR, IR - EUR, US - USD, GB - GBP, so this one lists all the countries and their associated currency).

I've been told to use a JOIN to link the tables, I need to grab all the countries a code is associated with and the location of those from country table.

I have little to no experience with SQL sadly, only basic queries, and database theory isn't a strong point.

Any advice, help, pointers welcome.

---

JOINS basically join two or more related tables together to make a bigger table. If you've used Excel, it's like a super VLOOKUP. The JOIN syntax augments your FROM clause. You specify the JOIN type (INNER, LEFT OUTER, RIGHT OUTER + other types and shorthand) and an ON condition (where the rows from the first table will be matched up to the second table).

Given your three tables, you should be able to bring in all the information you want with something like:

SELECT countries.name
       , currency_countries.country_code
       , currencies.name
       , currency_countries.currency_code
       , currencies.current_rate
  FROM currency_country
       INNER JOIN currencys
         ON currency_country.currency_code = currencys.code
       INNER JOIN countries
         ON currency_country.country_code = countries.code 

---

You should read up on simple DB/SQL stuff, it's all good and well getting the answer from here but at a bare min you should understand how to do things like join tables

This looks pretty good to start you off: http://www.sql-tutorial.net/SQL-tutorial.asp

Anyway, your SQL:

[EDIT - 2 posts beat me to the SQL ;) ]

---

I assume you have referential integrity between your tables, such that a Currency_Country rows do not exist without a Country row and at least one Currency row. In which case the Currency table does not need to be used.

To find all the countries with an associated currency, use:

SELECT Countries.country_code, location
FROM Countries INNER JOIN Currency_Country 
ON Countries.country_code = Currency_Country.country_code

---

You only need a currency_country table if you have a many-to-many relation between Currencies and Countries. Is that the case ? Otherwise, you just need a Currencies table, and a Countries table that includes the ISO code of the currency. (Note that some currencies have no country associated with them)