How do I force join inside a left join branch?

EDIT : This query returns you the result as expected from your sample data - (Also note, this query is taken with help from Treefrog's answer below) SELECT t1. Name, t3. A, t2.

Bob FROM T2 as t2 JOIN T3 as t3 ON t3. T2_id = t2. Id RIGHT JOIN T1 as t1 ON t1.Id = t2.

T1_id My Older answer SELECT a FROM T1 as t1 INNER JOIN T2 as t2 ON t1. Id = t2. T1_id LEFT JOIN T3 as t3 ON t2.

Id = t3. T2_id.

EDIT: This query returns you the result as expected from your sample data - (Also note, this query is taken with help from Treefrog's answer below) - SELECT t1. Name, t3. A, t2.

Bob FROM T2 as t2 JOIN T3 as t3 ON t3. T2_id = t2. Id RIGHT JOIN T1 as t1 ON t1.Id = t2.

T1_id My Older answer - SELECT a FROM T1 as t1 INNER JOIN T2 as t2 ON t1. Id = t2. T1_id LEFT JOIN T3 as t3 ON t2.

Id = t3. T2_id.

Sorry, but no, this won't enforce the join of t2 and t3 to be only when both have data in them. – Itay Moav Nov 19 '10 at 18:30 1 Some people forget we're not telepathic :( – OMG Ponies Nov 19 '10 at 18:38 @Italy Maov - Edited my answer. Also I am trying to help you here.

You don't have to downvote me. If you feel its wrong, then you can let me know. Will try to correct it or delete it if it does not help.

– Sachin Shanbhag Nov 19 '10 at 18:38 @Sachin Shanbhag - see edited question, I cancelled my down vote (I really consider down votes as something not personal, just marking wrong answer, sorry if I offended you). – Itay Moav Nov 19 '10 at 18:56 this still not correct... – Itay Moav Nov 19 '10 at 19:20.

SELECT T1. A FROM T2 JOIN T3 ON T3. T2_id = T2.Id RIGHT JOIN T1 ON T1.

Id = T2. T1_id.

Both of these will give you your result. Not sure which would perform better. NESTED STATEMENT: SELECT name, NULL AS a, NULL AS bob FROM t1 LEFT OUTER JOIN t2 ON t1.Id = t2.

T1_id LEFT OUTER JOIN t3 ON t2. Id = t3. T2_id WHERE t3.

T2_id IS NULL AND (SELECT COUNT(*) FROM t1 AS t1b LEFT OUTER JOIN t2 AS t2b ON t1b. Id = t2b. T1_id LEFT OUTER JOIN t3 AS t3b ON t2b.

Id = t3b. T2_id WHERE t1.Id = t1b. Id AND t3b.

A IS NOT NULL) = 0 UNION SELECT name, a, bob FROM t1 LEFT OUTER JOIN t2 ON t1.Id = t2. T1_id LEFT OUTER JOIN t3 ON t2. Id = t3.

T2_id WHERE t3. T2_id IS NOT NULL ORDER BY t1. Name TEMPORARY TABLE: CREATE TABLE #tmp_Rslt(name varchar(50), a varchar(50), bob varchar(50)) --select matches INSERT INTO #tmp_Rslt SELECT name, a, bob FROM t1 LEFT OUTER JOIN t2 ON t1.Id = t2.

T1_id LEFT OUTER JOIN t3 ON t2. Id = t3. T2_id WHERE t3.

T2_id IS NOT NULL ORDER BY t1. Name --select t1's that didn't have matches INSERT INTO #tmp_Rslt SELECT name, NULL AS a, NULL AS bob FROM t1 LEFT OUTER JOIN t2 ON t1.Id = t2. T1_id LEFT OUTER JOIN t3 ON t2.

Id = t3. T2_id WHERE t3. T2_id IS NULL AND t1.

Name NOT IN (SELECT DISTINCT name FROM #tmp_Rslt) SELECT * FROM #tmp_Rslt --cleanup. DROP TABLE #tmp_Rslt.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.