How to get same results without using distinct in query

I have a table with data like so:

[ID, Name]
1, Bob
1, Joe
1, Joe
1, Bob

I want to retrieve a list of records showing the relationship between the records with the same ID. For instance, I want the following result set from my query:

Bob, Joe
Joe, Bob
Bob, Bob
Joe, Joe
开发者_开发百科

This shows me the "from" and "to" for every item in the table.

I can get this result by using the following query:

SELECT DISTINCT [NAME] 
FROM TABLE A
INNER JOIN TABLE B ON A.ID = B.ID

Is there anyway for me to achieve the same result set without the use of the "distinct" in the select statement? If I don't include the distinct, I get back 16 records, not 4.

The reason you get duplicate rows without DISTINCT is because every row of ID = x will be joined with every other row with ID = x. Since the original table has (1, "Bob") twice, both of those will be joined to every row in the other table with ID = 1.

Removing duplicates before doing a join will do two things: decrease the time to run the query, and prevent duplicate rows from showing up in the result.

Something like (using MySQL version of SQL):

SELECT L.NAME, R.NAME
FROM (SELECT DISTINCT ID, NAME FROM A) AS L
INNER JOIN (SELECT DISTINCT ID, NAME FROM B) AS R
ON L.ID = R.ID

Edit: is B an alias for table A?

In SQL and MY SQL

SELECT COLUMN_NAME FROM TABLE_NAME group by COLUMN_NAME

Have you tried using a group by clause?

select name
from table a
inner join table b
on a.id=b.id
group by name

That should get you the same thing as your distinct query above. As for the result set that you want, a simple self join should do it:

select name1,name2
from(
  select id,name as name1
  from table
  group by 1,2
  )a
join(
  select id,name as name2
  from table
  group by 1,2
  )b
using(id)

Eliminating duplicate values with union without using distinct

Declare @TableWithDuplicateValue Table(Name Varchar(255))
Insert Into @TableWithDuplicateValue Values('Cat'),('Dog'),('Cat'),('Dog'),('Lion')

Select Name From @TableWithDuplicateValue

union

select null where 1=0

Go

Output
---------
Cat
Dog
Lion

For more alternate kindly visit my blog

http://www.w3hattrick.com/2016/05/getting-distinct-rows-or-value-using.html