CODEWITHSUNDEEP
sql
DGraham
DGraham

Reputation: 715

Selecting unique value SQL

When running this SQL query it returns each Find_ID four times, how can I make it only select unique finds? 

SELECT A.FIND_ID, B.NAME, PERIOD
FROM FINDS A, CLASS B
WHERE A.X >= 4
AND A.X <= 10
AND A.Y >= 4
AND A.Y <= 10
AND FIND_ID = DISTINCT

This returns

FIND_ID NAME                 PERIOD
========== ==================== ====================
         2 SHARD                BRONZE
         5 SHARD                BRONZE
         2 METAL_WORK           IRON_AGE
         5 METAL_WORK           IRON_AGE
         2 FLINT                MESOLITHIC
         5 FLINT                MESOLITHIC
         2 BONE                 RECENT
         5 BONE                 RECENT

Upvotes: 0

Views: 1947

Answers (2)

Mat Richardson
Mat Richardson

Reputation: 3606

If the two tables FINDS and CLASS are related you need to be using some kind of join (INNER, at a guess). The reason why you are getting four results is that you are running a query that returns the cartesian product of your results - that is, you will get all combinations of both tables joined together without a common field linking the two of them together.

Here's an example. Let's say you have the two really simple tables below:-

PersonID   Name
1           Matt
2           Fred

PersonID   Salary
1           23000
2           18000

Then a query like:-

SELECT * FROM Person, Salary

Would return something like:-

PersonID  Name  PersonID  Salary
1         Matt  1         23000
2         Fred  2         18000
1         Matt  2         18000
2         Fred  1         23000

Et voila, four records where you might expect two. Adding DISTINCT to this would achieve nothing, as each of the rows is distinct. To link the related tables you would need something like:-

SELECT * FROM Person INNER JOIN Salary ON Person.PersonID = Salary.PersonID

Upvotes: 0

PeterJohn
PeterJohn

Reputation: 589

Use the distinct property.. Select Distinct(SAMPLE) From table1

Upvotes: 2

Related Questions