Styjun

A Real World Example for Divide and Conquer Method

Do we have to introduce the character's name before using their names in a dialogue tag?

A bicolour masyu

How can I create an article with a title like the 1960s Journal of Finance?

How much did all the space agencies spent on rockets launching and space exploration? What are the benefits for me and you?

Remove side menu(right side) from finder

Brute-force the switchboard

How to split the polynomial .

Improving an O(N^2) function (all entities iterating over all other entities)

Ethiopian Airlines tickets seem to always have the same price regardless of the proximity of the date?

ISCSI, multiple initiaros for the same lun

Why did conquered countries after WWII recover, but countries conquered later continue suffering?

What's so great about Shalantha's Delicate Disk?

Why does Plot only sometimes use different colors for each curve

Can a creature sustain itself by eating its own severed body parts?

What does Windows' "Tuning up Application Start" do?

Book in which the "mountain" in the distance was a hole in the flat world

P-adic functions on annuli

What's the physical meaning of the statement that "photons don't have positions"?

Find position equal columns of matrix

RedirectTo deleting google 360 parameters from URL in Journey builder email sends

ESTA Travel not Authorized. Accepted twice before!

Why didn't NASA launch communications relay satellites for the Apollo missions?

Install suspension forks on non-suspension bike

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

How do I perform an IF…THEN in an SQL SELECT?Can I concatenate multiple MySQL rows into one field?How do I limit the number of rows returned by an Oracle query after ordering?SQL update query using joinsSQL Server query - Selecting COUNT(*) with DISTINCTSQL Server: How to Join to first rowHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?SQL select only rows with max value on a column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

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?

sql sql-server

share|improve this question

edited Mar 26 at 12:31

SyAu

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the primary key for your Table2?
  
  – Zack
  Mar 26 at 12:12
  

  
  
  
  

add a comment | 

2

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?

sql sql-server

share|improve this question

edited Mar 26 at 12:31

SyAu

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the primary key for your Table2?
  
  – Zack
  Mar 26 at 12:12
  

  
  
  
  

add a comment | 

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?

sql sql-server

share|improve this question

edited Mar 26 at 12:31

SyAu

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

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

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

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

| T2 | P10 | 
| T2 | P45 | 

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

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

.
.

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

sql sql-server

sql sql-server

share|improve this question

edited Mar 26 at 12:31

SyAu

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

share|improve this question

edited Mar 26 at 12:31

SyAu

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

asked Mar 26 at 11:55

SyAuSyAu

88066 gold badges2020 silver badges4040 bronze badges

4 Answers
4

active

oldest

votes

1

use distinct

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

share|improve this answer

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SyAu create fiddle not text dbfiddle.uk/…
  
  – Zaynul Abadin Tuhin
  Mar 26 at 12:13
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

0

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;

share|improve this answer

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:11
  

  
  
  
  

add a comment | 

0

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 

share|improve this answer

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55356561%2fsql-select-records-without-duplicate-on-just-one-field-in-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

use distinct

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

share|improve this answer

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SyAu create fiddle not text dbfiddle.uk/…
  
  – Zaynul Abadin Tuhin
  Mar 26 at 12:13
  

  
  
  
  

add a comment | 

1

use distinct

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

share|improve this answer

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @SyAu create fiddle not text dbfiddle.uk/…
  
  – Zaynul Abadin Tuhin
  Mar 26 at 12:13
  

  
  
  
  

add a comment | 

use distinct

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

share|improve this answer

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

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

share|improve this answer

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

answered Mar 26 at 11:57

Zaynul Abadin TuhinZaynul Abadin Tuhin

22.2k33 gold badges1414 silver badges3939 bronze badges

0

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

share|improve this answer

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

0

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

share|improve this answer

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

add a comment | 

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

share|improve this answer

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

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

share|improve this answer

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

answered Mar 26 at 11:59

The ImpalerThe Impaler

14.2k44 gold badges1717 silver badges4343 bronze badges

0

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;

share|improve this answer

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:11
  

  
  
  
  

add a comment | 

0

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;

share|improve this answer

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Please see my updated post
  
  – SyAu
  Mar 26 at 12:11
  

  
  
  
  

add a comment | 

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;

share|improve this answer

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

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

share|improve this answer

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

answered Mar 26 at 12:02

ElanochecerElanochecer

48533 silver badges88 bronze badges

0

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 

share|improve this answer

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

add a comment | 

0

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 

share|improve this answer

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

add a comment | 

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 

share|improve this answer

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

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 

share|improve this answer

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges

answered Mar 26 at 12:09

Hemang AgheraHemang Aghera

98611 silver badge1414 bronze badges