CODEWITHSUNDEEP
mysqlcodeigniter
Jorg Ancrath
Jorg Ancrath

Reputation: 1447

Trouble with MYSQL joins and count

I have two tables, X and Y

X:

Y:

I supply a user_id and a type_id, each user can have several entries in X with the same type_id.

I need to count all the entries in Y that match the criteria above. (hopefully I made myself clear ;))

So basically: From entries on X that match a user_id and type_id, grab all entries on Y that have the same x_id.

This is a query I was attempting in CodeIgniter (but any sort of MYSQL would be fine):

  $this->db->select("X.description, COUNT(Y.x_id) AS count ");
    $this->db->from("X");
    $this->db->join("Y", "Y.x_id=X.x_id");
    $this->db->where("X.type_id", 1);
    $this->db->where("X.user_id", 2);

This is only returning me 1 entry though.

Upvotes: 0

Views: 160

Answers (2)

Nil'z
Nil'z

Reputation: 7475

You can try this code:

select count(x_id) as total from y where x_id in ( select x_id from x where type_id = 'type_id_here' AND user_id = 'user_id_here'  )

In Codeigniter:

<?php
 $sql = "select count(x_id) as total from y where x_id in ( select x_id from x where type_id = 'type_id_here' AND user_id = 'user_id_here'  )";
 $rs = $this->db->query($sql);
 $data = $rs->row_array();
 $total = $data['total'];
?>

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Based on this "I need to count all the entries in Y that match the criteria above " you can try this:

SELECT 
    COUNT(*) 
FROM 
    x 
INNER JOIN y
    ON x.x_id = y.x_id
WHERE 
    x.type_id = 1
    AND x.user_id = 2

UPDATE: If you want to count per description you need to group by it as mentioned by Ed Gibbs:

SELECT 
    x.description,
    COUNT(*) 
FROM 
    x 
INNER JOIN y
    ON x.x_id = y.x_id
WHERE 
    x.type_id = 1
    AND x.user_id = 2
GROUP BY
    x.description

Upvotes: 1

Related Questions