join with where condition

i read many join questions here but unable to understand and create my own to get the right result i want.

i have three tables for now that is status,members,friends friend开发者_如何学Cs table have two columns friend_id and member_id

1. all three tables have member_id common primary id of members table
2. now i want to get all the status created by members and member's friends
3. if i have three members with id's 1,2,3
4. friends table have id's 1,2 so these two becomes friends of each other
5. 2 have 5 status updates and 1 have 2 status and 3 have 1 updates in status table
6. if i query against member 2 it should return 7 record...( 5 for 2 and 2 for 1 ) and should not return record of member 3.
7. if i query against member 1 it should return same record as for point 5.

do i need change in my tables structure ? please help how to get the record the way i want

How about a pre-query to the friends table for any qualifying member PLUS the member itself, then back-join to the rest of the tables...

select STRAIGHT_JOIN 
      PeopleList.Member_id,
      members.last_name,
      members.first_name,  (etc with any other fields)
      ms.status_id,
      ms.description (etc with any other fields from member_status table)
   from
      ( Select DISTINCT m.member_id
            from Members m
            where m.member_id = MemberDesiredVariable
        union select f.friend_id AS member_id
            from Friends f
            where f.member_id = MemberDesiredVariable
        union select f2.member_id
            from Friends f2
            where f2.friend_id = MemberDesiredVariable ) PeopleList
      join members
         on PeopleList.member_id = members.member_id
      join member_status ms
         on PeopleList.member_id = ms.member_id

This should get the primary person in question regardless of the person having ANY records in the "friends" table, such as a new person with no entries yet... they would at least qualify themselves and join to the members and member_status tables.

Then, in your scenario where member 1 is the criteria, it will query against the friends for any "Friend_IDs", and thus DISTINCT will have the 1 (direct from members) and the 2 where the member_id = 1, finds the Friend_id = 2. So now, this pre-query has two IDs and proceeds to get whatever the rest of your details you want.

The THIRD scenario is you want member 2... So, direct query to the members table guarantees their ID in the list to process, yet since their ID is NOT as a "MEMBER_ID" in the friends table, it has to look for itself as a "FRIEND_ID" from someone else and grab THAT Member's ID. So now, member 2 will also find member 1 and proceed to get details out.

As for member 3, if you queried against the Friends table, you'd get NO records at all, even IF the member 3 had some status records... It must be qualified against itself to be inclusive of the rest for processing... Yet will not find itself as a "member_id" nor "friend_id" in the friends table.

I couldn't actually test this at my current location, but logically should go no problem.

Finally, if you want the friends names REGARDLESS of having any "status" changes, change the last join to member_status to a LEFT JOIN.

--- Comment feedback

I can't suggest any books specifically, it just comes from years of experience... 1. UNDERSTAND THE RELATIONSHIP OF YOUR DATA...
2. Find out the inner-most "what do I want to get".
3. Throw all other elements out until you get the CRITERIA, not the CONTENT. 4. Keep your primary "get the criteria" up front... THEN Join in your other tables. 5. Then tack on all the other fields you want in the output result set

Trying to solve a complex query can very often be cluttered by all the OTHER elements of data a person is trying to get. Like so many other programming tasks... I like to make it work, then make it pretty. So too goes with querying. If your baseline query doesn't get the WHAT you want, it doesn't matter how many other tables you are joining together (left, outer, or normal join), your output will be wrong.

I've also added the clause "STRAIGHT_JOIN" to the sql at the top. This tells MySql to do the query in the order I've instructed it and don't have the optimizer try to think for me. This one clause has come in so frequently when joining a main table (such as millions of records) to "lookup" secondary tables that the query engine has falsely interpretted the lookup table as primary for querying which killed the performance...

Try to do some timed tests between the versions that work. If they are equally comparable, I would typically go with the one that I could understand in case I had to modify / change something in the future.

-- own records
SELECT member_id, friend_id, user_name, description
FROM
(SELECT M.member_id,
        M.member_id friend_id,
        M.user_name,
        MS.description
   FROM members M
   LEFT JOIN member_status MS on MS.member_id = M.member_id
UNION ALL
-- friends records
SELECT M.member_id,
       F.friend_id,
       MF.user_name,
       MS.description
  FROM members M
  JOIN (      SELECT friend_id member_id, member_id friend_id from friends 
        UNION SELECT member_id, friend_id from friends) F
                              ON F.member_id = M.member_id 
  LEFT JOIN member_status MS on MS.member_id = F.friend_id
  LEFT JOIN members MF       on MF.member_id = F.friend_id) R
WHERE R.member_id = 1

Here is the solution using UNION clauses. If the result if each SELECT is short (let's say less than 1000 rows) then it is faster than LEFT JOIN combined with a OR.

If by "friends of each other" you mean that you want :
  (a) the status of the members marked as friend
  +
  (b) the status of the members which the considered member is marked as friend
then you should use the tree UNION below.
If you want only (a) then delete the last UNION.

SELECT s.status_id
 FROM member_status AS s
 WHERE (s.member_id=@id)

UNION ALL

SELECT s.status_id
 FROM member_status AS s
 INNER JOIN friends AS f ON (s.member_id=f.friend_id)
 WHERE (f.member_id=@id)

UNION ALL

SELECT s.status_id
 FROM member_status AS s
 INNER JOIN friends AS f ON (s.member_id=f.member_id)
 WHERE (f.friend_id=@id)