# Sql - 'on'



## paul_carron (Oct 15, 2006)

In SQL what does 'ON' mean?

See the following code:


```
SELECT t.ISIN, t.SEDOL, t.PRIMARY_EXCH_CODE, t.PRICING_CURRENCY 
FROM motodev.ref_secmaster_details t
LEFT JOIN (SELECT ISIN, PRIMARY_EXCH_CODE, PRICING_CURRENCY
           FROM motodev.ref_secmaster_details
           GROUP BY  ISIN, PRIMARY_EXCH_CODE, PRICING_CURRENCY
           HAVING COUNT(*) > 1)t1
ON t1.ISIN = t.ISIN
AND t1.PRIMARY_EXCH_CODE = t.PRIMARY_EXCH_CODE
AND t1.PRICING_CURRENCY = t.PRICING_CURRENCY
LEFT JOIN (SELECT SEDOL, PRIMARY_EXCH_CODE, PRICING_CURRENCY
           FROM motodev.ref_secmaster_details
           GROUP BY  SEDOL, PRIMARY_EXCH_CODE, PRICING_CURRENCY
           HAVING COUNT(*) > 1)t2
ON t2.SEDOL = t.SEDOL
AND t2.PRIMARY_EXCH_CODE = t.PRIMARY_EXCH_CODE
AND t2.PRICING_CURRENCY = t.PRICING_CURRENCY
WHERE t1.ISIN IS NOT NULL
OR t2.SEDOL IS NOT NULL  
ORDER BY t.sedol
```


----------



## Chicon (Jul 29, 2004)

*ON* only applies to *JOIN*. As it expects a condition like *WHERE*, I guess the *ON* word was invented in order to prevent ambiguous or conflicting statements.


----------



## paul_carron (Oct 15, 2006)

Cheers Chicon,

The above SQL finds all records where the ISIN, PRIMARY_EXCH_CODE, and PRICING_CURRENCY fields are duplicated aswell as all records where the SEDOL, PRIMARY_EXCH_CODE, and PRICING_CURRENCY fields are duplicated. Would you know how to write a delete statement that will delete all the duplicate records?

Cheers
Paul


----------



## Chicon (Jul 29, 2004)

Unfortunately, it can't be done with only one statement.
There's an example of procedure on the following link :
http://www.sql-server-performance.com/articles/dba/delete_duplicates_p1.aspx


----------

