Get distinct column values based on composite primary key

Question

I've a table such that :

table : resource

resource_id | name
------------------
1           | res1
2           | res2


table : type

type_id | name
---------------
1       | type1
2       | type2

table : action

action_id | name
-----------------
1         | read
2         | add
3         | edit
4         | delete
5         | disable

And finally a table of their mapping

table : mapping

resource_id | type_id | action_id
------------------------------------
1           | 1       | 1
1           | 1       | 2
1           | 1       | 3
2           | 1       | 1
2           | 1       | 2
2           | 1       | 3
2           | 2       | 4
2           | 2       | 5

etc..

Now I made a query to get : (looping through each type_id)

for type_id = 1, I'm getting :

   resource_id | type_id
    ---------------------
    1           | 1
    1           | 1
    1           | 1

while I wish to get only one row

Similarly

for type_id = 2

I am getting :

resource_id | type_id
---------------------
2           | 1
2           | 1
2           | 1
2           | 2
2           | 2

while I wish to get only 2 rows : i.e.

resource_id | type_id
---------------------
2           | 1
2           | 2

.. and so on

This is my query :

 $this->db->select ( 'p.resource_id, p.type_id' );
 $this->db->from ( 'mapping p' );
 $this->db->join ( 'resources r', 'p.resource_id = r.resource_id' );
 $this->db->join ( 'type t', 'p.type_id = t.type_id' );
 $this->db->where ( 'p.type_id', $typeId );

I also tried : (in ref to : http://ellislab.com/forums/viewthread/57781/)

 $this->db->select ( 'DISTINCT p.resource_id, p.type_id' );
 $this->db->from ( 'mapping p' );
 $this->db->join ( 'resources r', 'p.resource_id = r.resource_id' );
 $this->db->join ( 'type t', 'p.type_id = t.type_id' );
 $this->db->where ( 'p.type_id', $typeId );

But distinct doesn't work and I get : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax.

Answer 1

In first query use group-ing: Group by resource_id, type_id

This will limit count of result lines.