CASE 
          WHEN child.ProID = '' OR child.ProID  IS NULL THEN  child.field1
          ELSE parent.field1
       END AS field1 
       CASE 
          WHEN child.ProID  = '' OR child.ProID  IS NULL THEN  child.field2
          ELSE parent.field2
       END AS field2,

COALESCE(child.field1, parent.field1),
COALESCE(child.field2, parent.field2)

ISNULL(child.field1, parent.field1),
ISNULL(child.field2, parent.field2)

quote:

---

Originally posted by DBA in the making

If you could replace all the empty strings in the child record with NULL, then you could use:

COALESCE(child.field1, parent.field1),
COALESCE(child.field2, parent.field2)

Coalesce return the first non-null argument passed to it. If the child.fieldx is null, then it will return parent.fieldx.

However, if the child field contains an empty string, then it will return that. You could also use:

ISNULL(child.field1, parent.field1),
ISNULL(child.field2, parent.field2)

which would achieve the same thing. ISNULL Does basically the same thing, but only takes 2 parameters. It would probably be faster the COALESCE, but I'm just guessing there.

There are 10 types of people in the world, those that understand binary, and those that don't.

---

DECLARE @parent TABLE(
	  PK     TINYINT
	, field1 VARCHAR(100)
	, field2 VARCHAR(100)
);
INSERT @parent SELECT 0,'pf1','pf2';

DECLARE @child TABLE(
	  FK TINYINT
	, IX TINYINT NOT NULL PRIMARY KEY
	, ProID  VARCHAR(100)
	, field1 VARCHAR(100)
	, field2 VARCHAR(100)
);
INSERT @child
	SELECT 0, 1, 'A'   ,'cf1' ,'cf2' UNION ALL -- test 1
	SELECT 0, 2, 'B'   ,'cf1' ,'cf2' UNION ALL -- test 2
	SELECT 0, 3, ''    ,'cf1' ,'cf2' UNION ALL -- test 3
	SELECT 0, 4, NULL  ,'cf1' ,'cf2' UNION ALL -- test 4
	SELECT 0, 5, ''    ,''    ,NULL  UNION ALL -- control 1; ProID is an empty string, 
--child fields should come through as empty/null, but will be replaced by parent fields instead
	SELECT 0, 6, NULL  ,NULL  ,''    UNION ALL -- control 2; ProID is null, 
--child fields should come through as empty/null, but the null value in child field 1 will be replaced by the parent field
	SELECT 0, 7, 'C'   ,''    ,NULL  UNION ALL -- control 3; works as advertized
	SELECT 0, 8, 'D'   ,NULL  ,''              -- control 4; works as advertized
SELECT 
	 [IX]		=child.ix
	,[ProID]	=child.ProID
	,[field1]	=ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(child.ProID, ''), ''), child.field1), child.ProID), parent.field1)
	,[field2]	=ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(child.ProID, ''), ''), child.field2), child.ProID), parent.field2)
FROM @parent parent JOIN @child child ON parent.PK=child.FK;

IX ProID F1  F2
1  A     pf1 pf2 -- correct
2  B     pf1 pf2 -- correct
3        cf1 cf2 -- correct
4  NULL  cf1 cf2 -- correct
5        pf1 pf2 -- F1 should be NULL, F2 should be ''
6  NULL  pf1     -- F1 should be '', F2 is correct
7  C     pf1 pf2 -- correct
8  D     pf1 pf2 -- correct

,CASE ISNULL(child.ProID, '') WHEN '' THEN child.field1 ELSE parent.field1 END AS field1 
,CASE ISNULL(child.ProID, '') WHEN '' THEN child.field2 ELSE parent.field2 END AS field2

quote:

---

Originally posted by yosiasz

Greetings

We have one table which has both parent records and child records.

---

quote:

---

Originally posted by PackRat

I believe I understand where you're trying to end up on this one; my appologies if not.

[code]
DECLARE @parent TABLE(
PK TINYINT
, field1 VARCHAR(100)
, field2 VARCHAR(100)
);
INSERT @parent SELECT 0,'pf1','pf2';

DECLARE @child TABLE(
FK TINYINT
, IX TINYINT NOT NULL PRIMARY KEY
, ProID VARCHAR(100)
, field1 VARCHAR(100)
, field2 VARCHAR(100)
);

---

create table parent(project int, ref int, field1 varchar(12), field2 int, amount money)
--'parent row'
INSERT INTO [dbo].[parent]
           ([project]
           ,[ref]
           ,[field1]
           ,[field2]
           ,[amount])
     VALUES
           (1
           ,222
           ,'Credit'
           ,12354
           ,50)
----'child rows' in same table
INSERT INTO [dbo].[parent]
           ([project]
           ,[ref]
           ,[field1]
           ,[field2]
           ,[amount])
     VALUES
           (NULL
           ,222
           ,'Credit'
           ,12354
           ,50)


INSERT INTO [dbo].[parent]
           ([project]
           ,[ref]
           ,[field1]
           ,[field2]
           ,[amount])
     VALUES
           (NULL
           ,222
           ,'Credit'
           ,12354
           ,56789)