Welcome Guest, Not a member yet? Register   Sign In
Joining tables (a little bit tricky)
#1

Hi guys, I have a small problem here with joining two tables and echoing result to the view. I'll try to explain it as simply as possible.

Table "members" - columns "id", "first_name", "last_name", "sex (male/female)"
Table "couples" - columns "id", "m_id", "f_id"

As you can see, IDs are primary keys and m_id (id for male), f_id (id for female) are foreign keys (tables are related).

Is there a way to join these tables and store the data (male first_name, last_name + female first_name, last_name) to echo it using foreach loop? Or do I have to edit my tables somehow? For me the problem is that there are two foreign keys so if I join the tables, I can store only males info or females info depending on which foreign key I use to join.

Any ideas? I would be thankful.
Reply
#2

(06-24-2015, 09:12 AM)tomop Wrote: Hi guys, I have a small problem here with joining two tables and echoing result to the view. I'll try to explain it as simply as possible.

Table "members" - columns "id", "first_name", "last_name", "sex (male/female)"
Table "couples" - columns "id", "m_id", "f_id"

As you can see, IDs are primary keys and m_id (id for male), f_id (id for female) are foreign keys (tables are related).

Is there a way to join these tables and store the data (male first_name, last_name + female first_name, last_name) to echo it using foreach loop? Or do I have to edit my tables somehow? For me the problem is that there are two foreign keys so if I join the tables, I can store only males info or females info depending on which foreign key I use to join.

Any ideas? I would be thankful.

You can join the tables twice in a SQL query

SELECT males.first_name, males.last_name, females.first_name, females.last_name, c.*
FROM couples c
INNER JOIN members males ON males.id = c.m_id
INNER JOIN members females ON females.id = c.f_id
Reply




Theme © iAndrew 2016 - Forum software by © MyBB