SQL Unique Values

Question

I have this query that joins 3 table. It appears to be working but I am getting duplicates. How can I remove the duplicates?

SELECT FIRST 100 e.email_id, e.location_id, e.email, l.location_type, p.salutation,
       p.fname, p.lname
FROM email e, location l, person p
WHERE e.location_id = l.location_id
AND l.per_id = p.per_id

Which version of Informix are you using? You should be able to use the SQL-92 explicit join notations instead of the implicit joins that you're using - unless you have a really old version of Informix (OnLine 5.20, SE 7.2x). You should also use the explicit joins in preference to the Informix-style OUTER join - I saw that in a question of yours which you have now deleted. — Jonathan Leffler, Oct 28 '11 at 23:24

score 4 · Accepted Answer · answered Oct 28 '11 at 21:31

4

The simple answer is to add DISTINCT to your query.

SELECT FIRST 100 DISTINCT e.email_id, e.location_id, e.email, l.location_type, p.salutation, p.fname, p.lname
FROM email e, location l, person p
WHERE e.location_id = l.location_id
AND l.per_id = p.per_id

answered Oct 28 '11 at 21:31

Joe Stefanelli

132,803
19
237
235

Adding DISTINCT just keeps it running forever even if I do SELECT FIRST 2 – Antarr Byrd Oct 28 '11 at 21:39
2

That's because the engine has to scan all rows to perform the de-duplication before returning any to the client. The FIRST N clause isn't going to achieve very much in this instance. – RET Oct 29 '11 at 04:52

score 2 · Answer 2 · answered Oct 28 '11 at 21:32

2

use Distinct

  SELECT FIRST 100 Distinct e.email_id, e.location_id, 
         e.email, l.location_type, p.salutation, 
         p.fname, p.lname 
  FROM email e, location l, person p 
  WHERE e.location_id = l.location_id AND l.per_id = p.per_id

answered Oct 28 '11 at 21:32

Charles Bretana

143,358
22
150
216

score 2 · Answer 3 · answered Oct 28 '11 at 23:30

Since you are doing straight inner joins, you only get duplicate entries in the result set if there are duplicate entries in the input tables.

SELECT FIRST 100 e.email_id, e.location_id, e.email, l.location_type, p.salutation,
       p.fname, p.lname
  FROM email AS e
  JOIN location AS l ON e.location_id = l.location_id
  JOIN person AS p ON l.per_id = p.per_id

The most likely place for there to be trouble is in the 'location' table. You could establish that with a query such as:

SELECT location_id, per_id, COUNT(*)
  FROM location
 GROUP BY location_id, per_id
HAVING COUNT(*) > 1;

If this returns any data, then you have a pointer to where the trouble is. You should then examine why you don't have a unique constraint on the combination of location_id, per_id.

SQL Unique Values

3 Answers3