Select Many-to-Many with more than one count?

Select Many-to-Many with more than one count? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Select Many-to-Many with more than one count? (/showthread.php?tid=35630)

Select Many-to-Many with more than one count? - El Forum - 11-05-2010

[eluser]Lockzi[/eluser]
Hi,

I would love to do the following:

select all rows where 'another_id' has more than one key_id but that key_id has no other.

In this example:

Code:
relation_id key_id another_id

1           1      5

2           3      5

3           7      6

4           5      9

5           21     13

6           5      19

7           5      26

8           3      26

PSUDO CODE

Code:
SELECT * FROM this_table WHERE another_id = 5 AND (each key_id in result DOESNOT have other another_id)

The results should be:

Code:
1           1      5

But NOT

Code:
2           3      5

Since the key_id 3 also has another_id = 26:

Code:
8           3      26

I've personally been facing this problem for a couple of days now.
What I've found so far is subqueries (mainly from here http://shawnmccool.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/, but I just can't make it work the way I want it.

Am I going about this all wrong?

Greatly appreciated input,
Lockzi