CODEWITHSUNDEEP
mysqlsql
Michaela
Michaela

Reputation: 493

SQL Query construction - multiple queries needed? Nested select?

This is being done in MySQL 5.

I have a list of members who have one or more: address, email and phone numbers. Each member can also have multiple friends, each of whom can also have address, email and phone numbers.

So my tables are (simplified number of fields for brevity):

member: id, firstName, lastName
address: id, city, personId 
phone: id, number, personId
email: id, address, personId
friend_x_member: id, memberId, friendId

I cannot figure out how to make one query where I get the member information with all of his addresses, phone numbers and email addresses along with his friends and all of their addresses, phone numbers, email addresses. I guess maybe I shouldn't really be doing it in one query?

This at least gets me the member information, I just need to figure out how to loop through the rows to get my objects populated right. But I don't know where to go from here.....

select * 
from member, address, email, phone, friend_x_member 
where member.id = '1' and 
      address.personId = member.id and 
      email.personId = friend.id and 
      phone.personId = friend.id and 
      friend_x_member.memberId = member.id

Upvotes: 1

Views: 594

Answers (2)

user359040
user359040

Reputation:

Try:

select * 
from member, address, email, phone
where member.id in (select '1' union all 
                    select friendId from friend_x_member 
                    where friend_x_member.memberId = '1') and 
      address.personId = member.id and 
      email.personId = friend.id and 
      phone.personId = friend.id and

Note that your existing query (and the above query) is using inner joins - so if any of the lookup tables do not have records for member '1', nothing will be returned. To get around this, try using outer joins instead:

select * 
from member
left join address on address.personId = member.id
left join email on email.personId = friend.id 
left join phone on phone.personId = friend.id
where member.id in (select '1' union all 
                    select friendId from friend_x_member 
                    where friend_x_member.memberId = '1')

Upvotes: 1

Diego
Diego

Reputation: 36166

you are on the right track, you just have to add the same tables again this time joining with the friend ID:

select *, P_friend.number as FirendsPhoneNumber
from member, address, email, phone, friend_x_member, phone P_friend
where member.id = '1' and address.personId = member.id and email.personId = friend.id and phone.personId = friend.id and friend_x_member.memberId = member.id and member.memberID=P_friend.friendId

this is messy yes, I would create a view with your main query and then join the view with the phone table again to get the friend's phone. Just for simplicity.

Upvotes: 0

Related Questions