Inner join Without duplicates, is it possible?

Question

Given these two tables Table A1 has two rows with the same value 'a'

A1
a
a

Table A2 has two rows with primary key value A,B and they are associated with 'a'

A2
PK col2
A   a
B   a

What I want is a join of A1 and A2 with this result

a A
a B

Obviously inner join doesn't work here. Is there a way to do this in SQL Server 2008?

Please ask anybody who is not associated with the problem to read your question and explain it to you. — TheVillageIdiot, May 08 '12 at 14:32
If you are joining on the value A, there is no way not to get two records without defining which one to pick. WHy do you have two As in the table and what determnines which one is the correct one? — HLGEM, May 08 '12 at 14:34
What have you *actually* tried and what results did it give? — FrustratedWithFormsDesigner, May 08 '12 at 14:34
@user714002 `SELECT A1.pk, A2.pk WHERE A1.pk = A2.col2` is the answer to your current question. — Joseph Victor Zammit, May 08 '12 at 14:35
You can get that result without using table A1 at all. Perhaps more sample data is called for. — Mike Sherrill 'Cat Recall', May 08 '12 at 14:56

score 9 · Answer 1 · answered May 08 '12 at 14:36

9

You can wipe out the duplicates by using DISTINCT

select distinct
  A1.col1,
  A2.PK
from
  A1
  inner join A2
          on A1.col1 = A2.col2

answered May 08 '12 at 14:36

njr101

9,381
7
37
55

score 1 · Answer 2 · answered May 08 '12 at 14:37

1

If distinct is not restricted

SELECT DISTINCT a.*, b.pk
FROM    A1 a
INNER JOIN A2 b ON (a.[test] = b.fk)

answered May 08 '12 at 14:37

TheVillageIdiot

38,965
20
129
186

Romil Kumar Jain · Answer 3 · 2012-05-08T14:49:33.410

0

There are no joining condition in the post, so we need to go for cross join. I have applied cross join and restrict the duplicate values using distinct.

Select distinct A1.Col1, A2.Pk
From A1 ,A2

edited May 08 '12 at 14:49

answered May 08 '12 at 14:36

Romil Kumar Jain

19,561
8
59
90

score 0 · Answer 4 · edited Nov 14 '19 at 11:07

0

"and restrict the duplicate values using distinct." at least in Postgres 9+ DISTINCT eliminates existing duplicates but not preventing or restricting its appearing.

edited Nov 14 '19 at 11:07

a_horse_with_no_name

497,550
91
775
843

answered Dec 27 '16 at 04:26

Ahnassi

1

score 0 · Answer 5 · edited Nov 14 '19 at 11:46

0

SELECT DISTINCT A.* 
FROM aTable AS A 
     INNER JOIN 
     bTable AS B USING(columnId)

edited Nov 14 '19 at 11:46

i.signori

566
2
16

answered Nov 14 '19 at 11:02

Roman Ivasyshyn

98
1
8

Inner join Without duplicates, is it possible?

5 Answers5

Linked