Joining to Derived Tables

How can I join to a derived table?
My derived table would look like...
(SELECT file_id,
MAX(DATETIME) AS MAXDATE
FROM DataTrac.dbo.NOTES
WHERE group_id = 'SRV'
GROUP BY file_id) MAXDTTM
and I would need to LEFT OUTER JOIN from a table called GEN matching by
file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
Let me know...
Thanks!Try,
select ...
from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
a.file_id = b.file_id
AMB
"wnfisba" wrote:

> How can I join to a derived table?
> My derived table would look like...
> (SELECT file_id,
> MAX(DATETIME) AS MAXDATE
> FROM DataTrac.dbo.NOTES
> WHERE group_id = 'SRV'
> GROUP BY file_id) MAXDTTM
> and I would need to LEFT OUTER JOIN from a table called GEN matching by
> file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
> Let me know...
> Thanks!|||Correction,
select ...
from
gen as a
left join
(select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
table
on a.file_id = b.file_id
AMB
"Alejandro Mesa" wrote:
> Try,
> select ...
> from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
> a.file_id = b.file_id
>
> AMB
> "wnfisba" wrote:
>|||Many thanks Alejandro!!!
Worked like a charm!!!
"Alejandro Mesa" wrote:
> Correction,
> select ...
> from
> gen as a
> left join
> (select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
> table
> on a.file_id = b.file_id
>
> AMB
> "Alejandro Mesa" wrote:
>

Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...

Joined view does not contain all colums

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHA\SQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

It looks ok to me. Does your simplified query (below) work?

Joe G wrote:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

|||

first thing, try running ALTER VIEW with their current defenitions. A view is built at runtime like a table. It has records in syscolumns etc. Thus this thread brings up the old "what does SELECT * return in a view" questions. I got this wrong not too long ago when IView with MSFT...I could have killed myself lol.

I am telling you this info. because whenever someone says "a column that I recently added is not showing up in view", this is usually the problem. rerun THE ALTERVIEW statement.

HTH,

Derek

if this does not solve your problem, let me know and I will dig into it w/you further.

|||

Derek, Thank you very much! That indeed was the solution to my problem. Thanks to Skippy also for responding.

Joe

Join Question

Is it possible to do something like the following:

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1JOIN New_Products.dbo.SupportCentersAs t2ON t1.BranchLike't2.SCName%'

We cannot join the tables like that. If you provide your requirement we can provide the query for that.

|||

Not a clean solution at all, but at least works, if you need it urgently:

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

If somebody knows a nicer way of doing it, please share :)

|||

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Needs to be:
SELECT

t1.Branch,

t2.SCName

FROM WWFE07BoothLeads t1

INNER JOIN New_Products t2 ON t2.t1ID = t1.ID

WHERE t1.ID = @.Id //Or whatever condition you want

Make sense?

|||

WoW!! That works great!! There are only 605 original records but the query produces 608. Not sure why that is.

What I am trying to accomplish is to update the original table (WWFE07BoothLeads ) with a branchId

Select t1.Branch, t2.SCName, t2.SCID
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Update WWFE07BoothLeads Set BoothId = ?(1) Where Branch = ?(Atlanta)

|||

Prob a data/query issue. Post your query.

|||

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1
JOIN New_Products.dbo.SupportCentersAs t2
ON t1.BranchLiket2.SCName+'%'

|||

Ok... so my original idea was correct.

Select t1.Branch, t2.SCName, SCID from WWFE07BoothLeads As t1
JOIN New_Products.dbo.SupportCenters As t2
ON t2.SCName Like t1.Branch+'%'

I had to modify it a little bit..

|||

Yes your original query is correct except 't1.Branch%' except specify the value in the completely single quote which is corrected now.

There is scope of getting more records than the actual join as you already mentioned in your message.

Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (Supplier) has 2 possible values
(Customer and Supplier) and the ContactID column will either have
CustomerID or SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from
the 3rd table (PhoneBook) along with the original names. I have come
up with this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably
a much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up
with a better queries.
Thanks in advance!
AbhinavHi,
You can use the CASE expression as bellow:
Select
P.PBID,
P.PhoneNumber ,
Case When P.Contacttype = 'Customer' Then C.CustomerName Else S.SupplierName
End As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID
Left Outer Join Supplier S On P.ContactID = S.SupplierID
Regards,
arik.
"abhinav50@.gmail.com" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (Supplier) has 2 possible values
> (Customer and Supplier) and the ContactID column will either have
> CustomerID or SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from
> the 3rd table (PhoneBook) along with the original names. I have come
> up with this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably
> a much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up
> with a better queries.
> Thanks in advance!
> Abhinav
>

Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (PhoneBook) has 2 possible values (Customer
and Supplier) and the ContactID column will either have CustomerID or
SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from the
3rd table (PhoneBook) along with the original names. I have come up with
this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably a
much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up with
a better queries.
Thanks in advance!
AbhinavHow are enforcing data integrity between the Customer and Supplier
tables and the PhoneBook table? Since you can't use a foreign key, [as
it references 2 separate tables], I assume you have a trigger? The FK is
more solid, btw. [if you don't have anything in the database to do this,
consider the fact that anyone that can add data to the database can add
data to this table that does not have valid referenced values.]
You really should have 2 separate phonebook tables, since you have 2
referenced tables (and therefore are representing 2 distinct phonebook
entities), with FKs.
That said, if you can't/won't change it, you can use a UNION ALL.
Select
P.PBID,
P.PhoneNumber ,
C.CustomerName As ContactName,
P.Contacttype
From
PhoneBook P
Join Customer C On P.ContactID = C.CustomerID
where
P.Contacttype = 'Customer'
UNION ALL
select
p.pbid,
p.phonenumber,
s.suppliername as ContactName,
P.contacttype
from
phonebook p
join Supplier S On P.ContactID = S.SupplierID
where
P.Contacttype = 'Supplier'
[to get one phonebook from the 2 i recommend, you could do the same thing]
A J wrote:
> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>|||Assuming you reject Trey's idea to have a schema that properly enforces
referential integrity. I don't think your sql is too bad. The only thing
that is "inelegant" is the ContactName portion. I think it should just be
something like this:
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, S.SupplierName) As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
In my opinion, this is better than the union all approach.
"A J" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>
>

Join problem

Hi

CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users] VALUES('Unal')
INSERT INTO [dbo].[Users] VALUES('Volkan')
INSERT INTO [dbo].[Users] VALUES('Duygu')
INSERT INTO [dbo].[Users] VALUES('Elif')
INSERT INTO [dbo].[Users] VALUES('Mehmet')
INSERT INTO [dbo].[Users] VALUES('Demir')

CREATE TABLE [dbo].[Agenda](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityName] [nvarchar](50) NULL,
[UserId] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Agenda] VALUES('school excursion','1,3,4')
INSERT INTO [dbo].[Agenda] VALUES('party','6,2,3,1')

Table Name : Users

Id UserName

1 Unal

2 Volkan

3 Duygu

4 Elif

5 Mehmet

6 Demir

---

Table Name : Agenda

Id ActivityName UserId

1 school excursion 1,3,4

2 party 6,2,3,1

---

I want to join Users table with Agenda table.

The Result has to be like below:

Id ActivityName UserId UserNames

1 school excursion 1,3,4 Unal, Duygu, Elif

2 party 6,2,3,1 Demir, Volkan, Duygu, Unal

How can I do it in SQL 2005. I don't want use cursor.

OR

if I can't do that join, I have to do other join that it's below.

Id ActivityName UserId UserNames

1 school excursion 1 Unal

1 school excursion 3 Duygu

1 school excursion 4 Elif

2 party 6 Demir

2 party 2 Volkan

2 party 3 Duygu

2 party 1 Unal

thanks so much for help

What you are attempting to accomplish is a form of denormalization. While it is not a 'straightforward' task in T-SQL, here are links to a couple of approaches that may work for you.

Lists -Field Concatenation, One Field to Itself for string
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks everbody who help and answer.

Join Problem

Hi,

I have 2 tables:

CREATE TABLE [dbo].[TBL_CONDITION](
[CONDITIONID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](500) NULL)

CREATE TABLE [dbo].[TBL_CONDITION_CUSTOMER](
[CCAN] [varchar](10) NOT NULL,
[CONDITIONID] [int] NOT NULL,
[FOLLOW-UP_DATE] [nchar](10) NOT NULL)

Sample data is as follows:

INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of statements')
INSERT INTO [tbl_condition] ([NAME])VALUES('Satisfactory review')
INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of latest interim')

INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',1,'03/09/2007')
INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',2,'04/09/2007')

Inrespective of data in child table ([tbl_condition_customer] ) I want all the rows from tbl_condition - I am using outer join for that but am not getting the required output:

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID
WHERE (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

But I am getting output as that of Inner Join?

What am I missing. I want output as follows:

ConditionId Name CCAN
--
1 Receipt of statements 52410
2 Satisfactory review 52410
3 Receipt of latest interim NULL

Hi JayaC

You could change your select statement to the statement shown below.

Chris

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID AND (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

|||Thanks Chris. That worked :)