Need to display total of records that have X as its venue_id

[eluser]Brad K Morse[/eluser]
I have two tables, data and venues, they relate by venues.id = data.venue_id

I want to list the title of the venue and to the right of it, the total number of people who are assigned to it from the data table.

So it would be:

Venue Title: total number of records in data table that have that same venue_id

I am unsure how to do this, but I think using num_rows() will do the trick and I would have to go through each record in the venues table, grab the id (venue.id), query the data table for where venues.id = data.venue_id, and print the num_rows() of that query.

That is probably what needs to be done, but I am unsure, any help is appreciated.

[eluser]vitoco[/eluser]
Something like this :
- study joins ( INNER , LEFT , RIGHT ) between tables
- study GROUP BY clause

Code:

$sql = "
SELECT
    venues.id ,
    venues.title ,
    COUNT( data.venue_id ) AS num_records_in_data_table
FROM
    venues
LEFT JOIN
    data
ON
    ( venues.id = data.venue_id )
GROUP BY
    venue.id
";

$query    = $this->db->query( $sql );

foreach( $query->result_array() as $row )
{
    print_r($row );
}
                
$query->free_result();

Saludos

[eluser]Brad K Morse[/eluser]
Thank you Vitoco. I definitely need to study up on joins. I appreciate it!