CODEWITHSUNDEEP
mysql
Katie Radisson
Katie Radisson

Reputation: 21

Selecting only the distinct(name) from one table where the ID is used in another?

I have a table called L1 with 3,000 records in it that has a field called town_id.

town_id in L1 is the integer ID of data in a second table called towns containing several hundred town names - the field name is name.

I need to get all the distinct town names from the towns table that have a record in L1 so that I select only the list of towns actually in use rather than list all of them.

Can anyone tell me how to structure that query please as it is driving me up the wall :)

Upvotes: 2

Views: 113

Answers (3)

FoolishSeth
FoolishSeth

Reputation: 4031

To connect one table to another the fastest way is usually a join. If you're writing queries it's a good idea to read and understand how joins actually work because it's central to relational databases. Once you join the two tables you can do a GROUP BY to collapse together a bunch of records that all share a common field value (like town name). So you could do:

select towns.name from towns inner join L1 on L1.town_id = towns.id group by towns.name

but for just getting distinct values people usually elect to use the 'distinct' operator instead:

select distinct towns.name from towns inner join L1 on L1.town_id = towns.id

Upvotes: 2

Tieson T.
Tieson T.

Reputation: 21231

Perhaps this will help you: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html - pretty sure you want an INNER JOIN.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754480

There are several ways to write it. The simplest, perhaps, is:

SELECT Name
  FROM Towns
 WHERE ID IN (SELECT Town_ID FROM L1)

Another is:

SELECT DISTINCT T.Name
  FROM Towns AS T
  JOIN L1 ON T.ID = L1.Town_ID;

Have fun inventing other ways...

Upvotes: 1

Related Questions