Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Simple SQL joining 2 tables - 2 many rows being returned

 
David McWilliams
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

SQL query here. I have 2 tables, customer and country. The customer table has a column called country_id, the primary key of country table. The country table is of the following shape:

Country:
country_id country_name
1 UK
2 Ireland
3 France

Customer:
Customer_id country_id
1 2
2 3
3 2

I need the following:
Customer_id country_name
1 Ireland
2 France
3 Ireland


I want to select all rows from the customer table whilst displaying the country name, not countryId.

Can anyone tell me how to do this? When I do a regular select I get every row in the customer table for every row in the country table.

Any ideas?

Thanks.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are different types of joins in the database world, INNER, OUTER and NESTED for example. Show us the query you are using and we can try to help.
 
David McWilliams
Ranch Hand
Posts: 77
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been trying the following but I am getting more rows than I bargained for:

select cust.customer_id, cou.country_name
from customer cust, country cou
where cust.country_id = cou.country_id
 
Matthew Brown
Bartender
Posts: 4568
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've just knocked up a test database to match that, and the query you've just given gives you what you want. The result you describe is what you'd get if you omitted the "where..." part of that query. Are you sure that's the query that's actually getting run?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic