Geek Answers Handbook

How to get the same results without using different queries

I have a table with such data:

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

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

 Bob, Joe Joe, Bob Bob, Bob Joe, Joe

This shows me the "from" and "to" for each element of the table.

I can get this result 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 set of results without using the "distinguishing" in the select statement? If I do not include the report, I get 16 records, not 4.

+4
source share
4 answers

The reason you get duplicate rows without DISTINCT is because every row ID = x will be connected to every other row with ID = x. Since the source table is twice (1, "Bob"), both of them will be connected to each row in another table with identifier = 1.

Removing duplicates before performing a join will do two things: reduce the query execution time and prevent duplicate rows from appearing as a result.

Something like (using the 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: B is an alias for table A?

+5
source

In SQL and MY SQL

 SELECT COLUMN_NAME FROM TABLE_NAME group by COLUMN_NAME
+4
source

Have you tried using the group by clause?

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

This should do the same as your distinct request above. Regarding the result set you want, a simple self-join should do this:

 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)
0
source

Eliminate duplicate values ​​by combining without using different

 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 a more alternative kind visit to my blog

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

-1
source

More articles:


All Articles