• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.