Return all values but only the same combination once.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of 100 values. I need to use three of the values from this
table once in the desired result. I want tho create a query that will return
all of the combinations possible without any duplicate combinations. For
example, I want to get 1,2,3 as a result but not 3,2,1 or 2,3,1...
I also do not want the query to give me 3,3,1 or 1,2,2. I can do this part
on my own by setting the criteria in the query to not return the undesired
results. It is the 1,2,3 - 3,2,1 dilema that is a problem for me.


Thanks,
 
Hi John

You can get what you want by specifying that the first value is less than
the second and that the second is less than the third.

The SQL would look something like...

SELECT tbl_values.myval, tbl_values_1.myval, tbl_values_2.myval
FROM tbl_values, tbl_values AS tbl_values_1, tbl_values AS tbl_values_2
WHERE (tbl_values.myval < tbl_values_1.myval)
AND (tbl_values_1.myval < tbl_values_2.myval)

hth

Andy Hull
 
A lot of records, that!

Unique Cartesian Product:
SELECT A.ID, B.ID, C.ID
FROM Table1 A, (SELECT ID FROM Table1 A1) B, (SELECT ID FROM Table1 A2) C
WHERE A.ID <> B.ID AND B.ID <> C.ID AND A.ID <> C.ID

HtH

Pieter
 
Thanks! I knew there was a simple fix.

Andy Hull said:
Hi John

You can get what you want by specifying that the first value is less than
the second and that the second is less than the third.

The SQL would look something like...

SELECT tbl_values.myval, tbl_values_1.myval, tbl_values_2.myval
FROM tbl_values, tbl_values AS tbl_values_1, tbl_values AS tbl_values_2
WHERE (tbl_values.myval < tbl_values_1.myval)
AND (tbl_values_1.myval < tbl_values_2.myval)

hth

Andy Hull
Click to expand...
 
And I don't know why I made it overly complicated <g>

SELECT A.ID, B.ID, C.ID
FROM Table1 A, Table1 B, Table1 C
WHERE A.ID <> B.ID AND B.ID <> C.ID AND A.ID <> C.ID

Pieter
 
Back
Top