Left outer join - where

Posted on 2010-01-04
Medium Priority
Last Modified: 2012-05-08
I am using LEFT OUTER JOIN to Table2. If t2.CId is not =@CId I do not want to return that row.
How is best to do that?

T1Id   T1Text
1          One
2          Two

T2Id    T1Id   CId   T2Text
1          2       1        Eins
2          2       2        Zwei


Result should be:

T1Id  T1Text    T2Id      CId       T2Text
1        One        NULL   NULL    NULL
2        Two        1           1            Eins
It shold not return:
2        Two        2          2            Zwei
Question by:johnkainn
  • 2
  • 2
LVL 26

Expert Comment

ID: 26170055
try this
SELECT T1Id, T1Text, T2Id, CId, T2Text
FROM Table1 T1
		LEFT OUTER JOIN Table2 T2 ON T1.T1Id = T2.T2Id
WHERE T2.CId = 1

Open in new window

LVL 19

Accepted Solution

Limbeck earned 2000 total points
ID: 26170117
SELECT T1Id, T1Text, T2Id, CId, T2Text
FROM Table1 T1
                LEFT OUTER JOIN Table2 T2 ON T1.T1Id = T2.T2Id AND T2.CId = 1
LVL 26

Expert Comment

ID: 26170356
my query gives exactly what you are looking for. Why did you choose the other solution?
LVL 19

Expert Comment

ID: 26170563
because your where clause overrules your left join :) give it a try in a testdatabae

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question