CODEWITHSUNDEEP
sqlsql-server
SyAu
SyAu

Reputation: 1681

SQL - Select records without duplicate on just one field in SQL?

Table 1

| Customer_ID | Template_ID  
---------------------
| C1   |  T1   |    
| C1   |  T2   |    

---------------------

Table 2 

---------------------
| Template_ID | Product_ID  
---------------------
| T1   |  P1   |    
| T1   |  P5   |    
| T1   |  P5   |    

| T2    |  P10   |    
| T2    |  P45   |

Expected Join query result:

------------------------------------------
| Customer_ID | Template_ID  | Product_ID  
------------------------------------------
| C1          |  T1          |  P1
| C1          |  T1          |  P5

| C1          |  T2          |  P10
| C1          |  T2          |  P45

.
.

For a template, I want to get only the unique Product_ID like above. Currently my query returns P5 twice like,

.
.
| C1          |  T1          |  P5
| C1          |  T1          |  P5
.
.

How can I handle this at the query level?

Upvotes: 2

Views: 68

Answers (4)

Hemang A
Hemang A

Reputation: 1012

Please try this.

SELECT 
    DISTINCT A.Customer_ID ,A.Template_ID  ,B.Product_ID   
FROM 
    table1 AS A
INNER JOIN table2 AS B
ON A.Template_ID   = B.Template_ID

Upvotes: 0

Elanochecer
Elanochecer

Reputation: 610

You just have to GROUP BY the field you want to be unique, so Product_ID:

SELECT Customer_ID,  Template_ID, Product_ID
FROM table1
JOIN table2 using ( Template_ID )
GROUP BY Product_ID;

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

Use DISTINCT to eliminates duplicates. It does not apply to the first column only, but to the whole row.

For example:

select distinct t1.customer_id, t1.template_id, t2.product_id
from t1
join t2 on t2.template_id = t1.template_id

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

use distinct

  select distinct t1.*,t2.productid
  from table1 t1 join table2 t2 on t1.Template_ID  =t2.Template_ID

Upvotes: 1

Related Questions