Problem with left join

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksBetter if you post some ddl, sample data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>|||Aleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

Problem with Intead of Insert Trigger

I'm having a rather weird problem with an Instead Of Insert trigger.
Its supposed to check to see if the record exists in the destination table,
and if so update it, otherwise insert the row in the table. All pretty
straightforward, but I'm getting weird behavior.
If the destination table is empty, each row gets properly inserted. If there
are records in the destination table, any that are found get properly
updated, but no new records get inserted.
Here is my table layout:
CREATE TABLE [dbo].[TestProspect] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProspectNumber] [int] NULL ,
[iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
And here is my trigger:
CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
INSTEAD OF INSERT
AS
--try to update the inserted record to an existing record
UPDATE p
SET
pLastName = ISNULL(i.LastName,''),
pFirstName = ISNULL(i.FirstName,''),
pPhone = ISNULL(i.Phone1,''),
Type = 'UPDATE'
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName
WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
--if it isn't in the table, insert it
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
I've tried all kinds of things, like wrapping it in an if-else clause or
rearranging the insert/update order, to no avail. Anybody see something that
I'm missing?
Thanks in advance.Verify that ANSI_NULLS was set ON when the trigger was created. Also, if
{iFirstName, iLastName, iPhone} is a key, then they shouldn't be nullable,
and you should create a unique constraint to ensure that you don't get any
duplicates.
"Brian Levine" <Brian Levine@.discussions.microsoft.com> wrote in message
news:462FFE90-CF5E-4575-B357-2B7D64E3B382@.microsoft.com...
> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination
> table,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If
> there
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something
> that
> I'm missing?
> Thanks in advance.|||Try this for the insert portion:
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT 1
FROM TestProspect p WHERE
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
"Brian Levine" wrote:

> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination table
,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If the
re
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something th
at
> I'm missing?
> Thanks in advance.