double left join causing confusion

[matt2012]

I have 3 tables.

• Table 1 all my members
  
• Table 2 list of those members who have subscribed
  
• Table 3 list of those members who have cancelled their subscription
  

 

someone who cancels may rejoin and then cancel again so both table 2 and table 3 can have multiple entries for the same member. I am interested in getting all members from table 1 and getting the latest subscription information for them from table 2 and the end date from table 3 if it exists for that subscription.

 

table 1 [table1_id, name]

table 2 [table2_id, table1_id, start]

table 3 [table3_id, table2_id, end]

 

Below is what I tried but it is not associating the correct records of T3 with T2

 

Select T1.id, T1.name, Max(T2.start), end
FROM  T1
LEFT JOIN T2 ON T1.T1id = T2.T1id
LEFT JOIN T3 ON T3.T2id = T2.T2id
GROUP BY T1.id

 

any help appreciated!

[bubblegum.anarchy]

Consider altering the table definition if at all possible:

 

CREATE TABLE member (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL DEFAULT 'undefined'
);

CREATE TABLE subscription (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     member_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
     start_date DATE NOT NULL DEFAULT '0000-00-00',
     end_date DATE NULL DEFAULT NULL,
     INDEX FK_MEMBER_ID (member_id)
);
[code]

Then something like this would probably be wrong:

[code]
SELECT member.id, member.name, latest_subscription.start_date, latest_subscription.end_date
FROM member
     INNER JOIN ( SELECT * FROM subscription ORDER BY start_date DESC ) AS latest_subscription ON member.id = member_id
GROUP BY member.id;

 

Where as this would be just wacky:

 

SELECT member.id, member.name, latest_subscription.start_date, latest_subscription.end_date
FROM member
     INNER JOIN ( 
          SELECT * FROM subscription INNER JOIN ( 
               SELECT member_id, max(start_date) AS max_start_date FROM subscription GROUP BY member_id 
          ) AS max_subscription ON subscription.member_id = max_subscription.member_id AND subscription.start_date = max_subscription.max_start_date
     ) AS latest_subscription ON member.id = latest_subscription.member_id;

[/code][/code]