Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Table joins

Quote Reply
Table joins
I have some custom tables of products which belong to the links and I'm a bit stuck with joining tables.

I have set a foriegn key relation between ID in the Links table and LinkID in the custom table. However, when I want to select products from the custom table that are in a specific category, I want to select from (Custom, CatLinks) with the join on LinkID. I have this set up as a foreign key relation but I don't think this is a good idea as I don't want anything deleted from the CatLinks table when I delete products from the Custom table or vice versa. I suppose that I could do the select on (Custom,Links,CatLinks) but this seems a bit of a waste.

Is there a Links command to join two tables which don't have a foreign key relation?

Thanks, Laura.
Quote Reply
Re: [afinlr] Table joins In reply to
If you have a foreign key between ID (Links table) => LinkID (Custom Table) then the Links table also has a foreign key with the CatLinks table, so if you use:

$DB->table('Custom', 'Links', 'CatLinks')

...then it should work out because it will select records from the Links table where LinkID matches ID and also where LinkID (from the CatLinks table) matched ID in the Links table.

If that makes any sense...
Quote Reply
Re: [Paul] Table joins In reply to
Thank Paul. I had thought of that as an option. That means that if I also need some information about the Category I would need a select on 4 tables. Doesn't this have a noticeable effect on performance? If not, then this is what I'll do - I just wanted to make sure that I was using the most efficient method.
Quote Reply
Re: [afinlr] Table joins In reply to
You can improve performance by only selecting the columns you need from each table, inside the select() method, eg..

$tables->select('Col1', 'Col2' ... );
Quote Reply
Re: [Paul] Table joins In reply to
Ok. Thanks.