MySQL 3 Table join question...

[eluser]f244[/eluser]
Hi everyone,

I'm a newbie to CodeIgniter, really enjoying working with it so far. I have a question around MySQL joins across multiple tables. I've googled it, checked the user guide and searched the forum but haven't found another situation similar enough to solve my problem.

I'm working on a site for a record shop. Each item in the shop's catalogue has a single format (eg. CD album, 7" single etc.) but can potentially have more than one artist associated with it.

I have 3 tables:

catalogue (id, title, artist, artist2, artist3, artist4, etc...)
artists (id, name, etc...)
formats (id, format, etc...)

I have this query:

Code:

function getCatalogueItem($id)
    {    
    $data = array();
    $this->db->select('catalogue.id, catalogue.title, catalogue.artist, artists.id, artists.name, formats.format');
    $this->db->from('catalogue', 'artists', 'formats');            
    $this->db->join('artists', 'catalogue.artist = artists.id', 'left');        
    $this->db->join('formats', 'catalogue.format = formats.id', 'left');    
    $this->db->where('catalogue.id =', $id);        
    $Q = $this->db->get();    
    if ($Q->num_rows() > 0){
        foreach ($Q->result_array() as $row){
            $data[] = $row;
        }
    }
    $Q->free_result();
    return $data;
    }

This works perfectly for catalogue items with a single artist.

I'm trying to understand how to modify this query if there is an artist2, artist3 or artist4 for a catalogue item.

I'd like to return an artist name and id for each artist associated with a catalogue item.

Any thoughts or suggestions would be very much appreciated!

Steve.