# Query records' combinations

**URL:** <https://forum.kirupa.com/t/query-records-combinations/204873>\
**Category:** programming\
**Created:** [October 24, 2006, 7:32am UTC](https://forum.kirupa.com/t/query-records-combinations/204873 "2006-10-24T07:32:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![amaze](https://avatars.discourse-cdn.com/v4/letter/a/13edae/32.png) [@amaze](https://forum.kirupa.com/u/amaze)\
**Post date:** [October 24, 2006, 7:32am UTC](https://forum.kirupa.com/t/query-records-combinations/204873/1 "2006-10-24T07:32:32Z")

</div>

I got a simple SELECT query:

**SELECT id  
FROM myTable  
WHERE id \<= x;**

and the results are:  
1  
2  
3  
4  
.  
.  
x

and what i need is the combinations of (x-1), (x-2), …, 2

Let’s say x = 4.  
The combinations of 3 and 2 records would be:

1,2,3  
1,2,4  
1,3,4  
2,3,4

AND

1,2  
1,3  
1,4  
2,3  
2,4  
3,4

Anyone has any idea of how this is possible?

PS: I’m using ColdFusion, but try any language serves you and I’ll translate it. :glasses:

Thanks 🙂

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [October 24, 2006, 1:42pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/2 "2006-10-24T13:42:26Z")

</div>

[QUOTE=amaze;1984156]Let’s say x = 4.  
The combinations of 3 and 2 records would be:

1,2,3  
1,2,4  
1,3,4  
2,3,4

AND

1,2  
1,3  
1,4  
2,3  
2,4  
3,4  
[/QUOTE]^ i’m lost as to what this means. can you give column titles to these values?

---

<div class="post-metadata">

**Author:** ![amaze](https://avatars.discourse-cdn.com/v4/letter/a/13edae/32.png) [@amaze](https://forum.kirupa.com/u/amaze)\
**Post date:** [October 24, 2006, 1:55pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/3 "2006-10-24T13:55:01Z")

</div>

I only take 1 column and the column’s name is “id”.

the records/values I get from the SELECT QUERY are:  
1(id=1)  
2(id=2)  
3(id=3)  
4(id=4)

From these 4 records I also want to get the triads and couples…  
It’s for a soccer bet application, where someone selects lets say 4 games and wants to bet on all triads(4 of them).

This is for x=4. I want the coding for any x.

Is this more clear? :sigh:

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [October 24, 2006, 2:20pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/4 "2006-10-24T14:20:11Z")

</div>

ah yes. for testing purposes, table name in my example is amaze\_games:

```auto

/* gets first result set */
SELECT	a.id,
		b.id,
		c.id
FROM	amaze_games a
INNER JOIN	amaze_games b
ON a.id != b.id
AND a.id < b.id
INNER JOIN amaze_games c
ON b.id != c.id
AND b.id < c.id

/* gets second result set */
SELECT	a.id,
		b.id
FROM	amaze_games a
INNER JOIN	amaze_games b
ON a.id != b.id
AND a.id < b.id

```

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [October 24, 2006, 2:33pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/5 "2006-10-24T14:33:07Z")

</div>

actually, you can probably take out the != conditions and just use the tbl1.id \< tbl2.id

---

<div class="post-metadata">

**Author:** ![amaze](https://avatars.discourse-cdn.com/v4/letter/a/13edae/32.png) [@amaze](https://forum.kirupa.com/u/amaze)\
**Post date:** [October 24, 2006, 2:35pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/6 "2006-10-24T14:35:15Z")

</div>

1. how do i get/output the results?
2. how do i use it for x records? Not only for 4…
3. Thanks again 😃

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [October 24, 2006, 2:45pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/7 "2006-10-24T14:45:47Z")

</div>

1. that depends on what language your site is written in. i don’t know how to do it in CF.
2. hmm. i’m not sure how to approach using just SQL. not sure if it’s possible. it might be faster though to bring a [font=monospace]SELECT DISTINCT id FROM amaze\_games[/font] into an array in your server-side language, then create the permutations there.
3. you’re welcome

---

<div class="post-metadata">

**Author:** ![amaze](https://avatars.discourse-cdn.com/v4/letter/a/13edae/32.png) [@amaze](https://forum.kirupa.com/u/amaze)\
**Post date:** [October 24, 2006, 2:53pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/8 "2006-10-24T14:53:37Z")

</div>

These permutations are what I’m still looking for.

Thanks for the try. 🙂

---

<div class="post-metadata">

**Author:** ![bwh2](https://avatars.discourse-cdn.com/v4/letter/b/8c91f0/32.png) [@bwh2](https://forum.kirupa.com/u/bwh2)\
**Post date:** [October 24, 2006, 3:00pm UTC](https://forum.kirupa.com/t/query-records-combinations/204873/9 "2006-10-24T15:00:25Z")

</div>

i’ll think about it and let you know if anything comes to mind.