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?

Improve this question
1
  • What is the primary key for your Table2? Commented Mar 26, 2019 at 12:12

4 Answers 4

Reset to default
1

use distinct

 select distinct t1.*,t2.productid from table1 t1 join table2 t2 on t1.Template_ID =t2.Template_ID
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@SyAu create fiddle not text dbfiddle.uk/…
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
Improve this answer

Comments

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;
Improve this answer

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.