aspose file tools*
The moose likes JDBC and the fly likes Simple SQL joining 2 tables - 2 many rows being returned Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple SQL joining 2 tables - 2 many rows being returned" Watch "Simple SQL joining 2 tables - 2 many rows being returned" New topic
Author

Simple SQL joining 2 tables - 2 many rows being returned

David McWilliams
Ranch Hand

Joined: Mar 14, 2009
Posts: 73
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

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
David McWilliams
Ranch Hand

Joined: Mar 14, 2009
Posts: 73
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

Joined: Apr 06, 2010
Posts: 4466
    
    8

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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simple SQL joining 2 tables - 2 many rows being returned