CodeIgniter Forums
MySQL three table join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: MySQL three table join (/showthread.php?tid=90406)

MySQL three table join - bobw - 03-13-2024

I'm starting to tear my hair out over this one - well I would if I had any to begin with!
I've three tables in my database that I wish to query. Two of the tables, members and helpers, are data with the third, membershelpers, being a link table. The database isn't normalised, may be a problem, but this worked in CI3 and the database schema hasn't changed.
 
Code:
members{
    id,
    given_name,
    family_name,
    membership_number
}

helpers{
    id,
    given_name,
    family_name,
    membership_number
}

membershelpers{
    id
    members_id
    helpers_id
}
I'm wanting to list the following info for helpers for a given member. However some helpers are also members.

Code:
members.id
helpers.given_name
helpers.family_name
helpers.membership_number


I'm starting from a members.id value, passed in to a binding query via :id:
Code:
SELECT h.membership_number, h.given_name, h.family_name,  FROM helpers AS h  JOIN membershelpers mh ON mh.helper_id = p.id WHERE mh.contender_id = :id:  ORDER BY h.family_name ASC, h.given_name ASC

What I'm wanting in the above query is to also have the members.id value so I can create a link with it to jump to a page displaying info about that member. The CI3 code was
Code:
$h = new Helper();

$h->where('membership_number' , $membership_number);

$h->include_join_fields();
$h->include_related('members', array('id', 'given_name', 'family_name', 'membership_number'), TRUE);
$h->order_by("members.membership_number asc");

I've got the feeling I've gone down the wrong rabbit hole
Typical!

Figured it out. Added

(SELECT id FROM members m WHERE m.given_name = h.given_name AND m.family_name = h.family_name) AS helper_id

to the fields being selected.