Fill in missing values when joining tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fill in missing values when joining tables

Author

Topic

orangefan08
Starting Member

10 Posts

Posted - 2010-08-11 : 10:05:32

Hello all!

My existing tables look something like this

table 'coverages'

PolID CovID ProductID
123 1 P20
123 2 P30

table 'abc'

PolID CovID
123 1
123 2
123 3
123 4

and I'm trying join them in a way that produces this

PolID CovID ProductID
123 1 P20
123 2 P30
123 3 P30
123 4 P30

Basically, when a combination of polID and CovID is not included in the 'coverage' table, I want the ProductID column to assume the value where PolID are the same and the CovID is max.

Here is what I have so far but it is still just giving me null values....

select

Case When c.PolID is null then a.polid else c.polid end as PolID,
Case When c.CovID is null then a.CovID else c.polid end as CovID,
Case When c.productID is null then temp1.productID else c.ProductID end as ProductID

from coverages as c
right outer join abc as a on c.polid=a.polid and c.covid=a.covid
left outer join (select PolID, ProductID
from coverages as mb1
where coverageID = ( select max(coverageID) from coverages as mb2 where mb1.policyid = mb2.policyid)
group by PolID, ProductID) as temp1 on c.polid=temp1.polid

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 10:18:58

Here's one way

select a.PolID,a.CovID,coalesce(b.ProductID,c.ProductID)
from abc a
left join coverages b on a.PolID = b.PolID and a.CovID = b.CovID
left join (select PolID,MAX(ProductID) as ProductID from coverages group by PolID) c on a.PolID = c.PolID

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-11 : 10:23:06

There is no relationship between the values.

And even if you wanted to do a hack that did a substring on the PolID

3 & 4 don't relate



CREATE TABLE myCoverages99 (
PolID varchar(10), CovID char(3), ProductID varchar(10))

CREATE TABLE myabc99 (PolID varchar(10), CovID char(3))
GO

INSERT INTO myCoverages99 (PolID, CovID)
SELECT '123 1', 'P20' UNION ALL
SELECT '123 2', 'P30' 

INSERT INTO myabc99 (PolID)
SELECT '123 1' UNION ALL
SELECT '123 2' UNION ALL
SELECT '123 3' UNION ALL
SELECT '123 4'
GO

SELECT *
      FROM myCoverages99 c 
INNER JOIN myabc99 a
		ON (c.PolID = a.PolID
			 OR (     SUBSTRING(c.PolID,1,3) = SUBSTRING(a.PolID,1,3)
				 AND  SUBSTRING(c.PolID,5,1) = SUBSTRING(a.PolID,5,1)-2
				 )
			)
GO
			
DROP TABLE myCoverages99, myabc99
GO

Please say no

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

orangefan08
Starting Member

10 Posts

Posted - 2010-08-11 : 10:24:56

quote:

Originally posted by vijayisonly

Here's one way

select a.PolID,a.CovID,coalesce(b.ProductID,c.ProductID)
from abc a
left join coverages b on a.PolID = b.PolID and a.CovID = b.CovID
left join (select PolID,MAX(ProductID) as ProductID from coverages group by PolID) c on a.PolID = c.PolID

I want the ProductID where policyID are equal but then only look at where covID is maximum.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 10:37:01

quote:
Originally posted by orangefan08

quote:
Originally posted by vijayisonly

Here's one way
select a.PolID,a.CovID,coalesce(b.ProductID,c.ProductID)
from abc a
left join coverages b on a.PolID = b.PolID and a.CovID = b.CovID
left join (select PolID,MAX(ProductID) as ProductID from coverages group by PolID) c on a.PolID = c.PolID

I want the ProductID where policyID are equal but then only look at where covID is maximum.

So if CovID "2" has a value P10..then you want 3 and 4 to be valued with "P10"?

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-11 : 10:37:42

Ahh..you need to use code tags...I got completely lost there

code tags are like

[ code] ...some code [ /code]

You just remove the leading space after the first bracket

like this


table 'coverages'

PolID    CovID   ProductID
    123             1               P20
    123             2               P30

table 'abc' 

PolID    CovID   
    123             1               
    123             2  
    123             3
    123             4


and I'm trying join them in a way that produces this            

PolID    CovID   ProductID
    123             1               P20
    123             2               P30
    123             3               P30
    123             4               P30

ok..back to the drawing board...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 10:43:39

[code]SELECT a.PolID,a.CovID,c.ProductID
FROM abc a
CROSS APPLY (SELECT TOP 1 ProductID
FROM coverages
WHERE PolID= a.PolID
AND CovID <= a.CovID
ORDER BY CovID DESC) c
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 10:47:05

quote:
Originally posted by visakh16

SELECT a.PolID,a.CovID,c.ProductID
FROM abc a
CROSS APPLY (SELECT TOP 1 ProductID
FROM coverages
WHERE PolID= a.PolID
AND CovID <= a.CovID
ORDER BY CovID DESC) c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Very Nice.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 10:52:50

tnx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

orangefan08
Starting Member

10 Posts

Posted - 2010-08-11 : 12:48:37

quote:
Originally posted by visakh16

tnx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Simply awesome sir!

Thank you very much!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 12:51:55

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources