Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL: self join using each rows only once [duplicate]

Possible Duplicate:
combinations (not permutations) from cross join in sql

I've currently got a table with the following records:

A1
A2
A3
B1
B2
C1
C2

Where the same letter denotes some criteria in common (e.g. a common value for the column 'letter'). I do a self join on the criteria as follows:

SELECT mytable.*, self.* FROM mytable INNER JOIN mytable AS self 
   ON (mytable.letter = self.letter and mytable.number != self.number);

This join gives something like the following:

A1 A2
A2 A1
A1 A3
A3 A1
A2 A3
A3 A2
B1 B2
B2 B1
C1 C2
C2 C1

However, I only want to include each pair once (a combination instead of a permutation). How would I get the following:

A1 A2
A1 A3
A2 A3
B1 B2
C1 C2
like image 613
EoghanM Avatar asked Sep 17 '11 06:09

EoghanM


People also ask

How do you avoid duplicates in a self join?

Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.

How do you find duplicate records using self join in SQL?

Check for Duplicates in Multiple Tables With INNER JOINUse the INNER JOIN function to find duplicates that exist in multiple tables. Sample syntax for an INNER JOIN function looks like this: SELECT column_name FROM table1 INNER JOIN table2 ON table1. column_name = table2.

How do I join two tables in SQL without duplicates?

The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned). Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

Will inner join remove duplicates?

if join two tables using inner join method will it return duplicate values ? The answer is yes, if there are any. If there are duplicate keys in the tables being joined.


1 Answers

Changing the JOIN condition slightly will achieve what you want..

Instead of:

ON (mytable.letter = self.letter and mytable.number != self.number)

use

ON (mytable.letter = self.letter and mytable.number > self.number)

This will only include combinations where self.number is greater than mytable.number which in effect restricts the results to one valid ordering of each combination...

like image 66
Phill_P Avatar answered Oct 17 '22 07:10

Phill_P