Many to Many Join Query in CodeIgniter

Question

All previous attempts at a JOIN have left me with either the disc or item data populating the id, title keys of the result (maybe a clash is occuring).

So I have:

item table
fields: id, title

disc table
fields: id, title

item_disc table
fields: item_id, disc_id

How do I construct a double join so that I can access the related discs to an item. At present I am running two seperate queries, one for a single item record, then one for array of discs.

Apart from it being easier to do in one query with joins, if I ever want to retrieve more than 1 record, I would currently need to be running 2 queries per item as I cannot join my data.

Please help!! I feel this is my final many to many hurdle (though dare not speak too soon).

Answer 1

because both item and disc have columns named id and title, they will overwrite each other. you would need to use as to distinguish them...

for all items and their associated discs:

select m.*, i.title as item_title, d.title as disc_title
from item i, disc d, item_disc m where i.id = m.item_id and
d.id = m.disc_id order by i.title, d.title

for one item and its discs:

select m.*, i.title as item_title, d.title as disc_title
from item i, disc d, item_disc m where i.id = m.item_id = ? and
d.id = m.disc_id order by d.title

replace ? with the item you want