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
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.
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.
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.
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.
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With