Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A cpouple of OUTER JOINs, but want only one row from each

Status
Not open for further replies.
daveroam

daveroam

Programmer
Nov 28, 2006
10
GB
Hi,

I have this query
Code: 
SELECT 
 c2.id_number AS Cusip, c1.id_number AS Isin, c3.id_number AS Sedol, 
 c4.id_number AS Cusip144a, c5.id_number AS Isin144a, 
 ISNULL(fii.party_short_name, fii.party_long_name) AS DESCRIPTION,  
 bintostr(a.asset_id) AS ASSET_ID
FROM govcorp..asset a LEFT JOIN 
   (SELECT asset_id, id_number, MAX(id_eff_dt) AS id_eff_dt FROM govcorp..asset_ident WHERE id_cd='ISN' 
    GROUP BY asset_id,id_cd,id_number) c1 ON a.asset_id=c1.asset_id
   LEFT JOIN
   (SELECT asset_id,id_number,MAX(id_eff_dt) AS id_eff_dt FROM govcorp..asset_ident WHERE id_cd='CSP' 
    GROUP BY asset_id,id_cd,id_number) c2 ON a.asset_id=c2.asset_id
   LEFT JOIN
   (SELECT asset_id,id_number,MAX(id_eff_dt) AS id_eff_dt FROM govcorp..asset_ident WHERE id_cd='SED' 
    GROUP BY asset_id,id_cd,id_number) c3 ON a.asset_id=c3.asset_id 
   LEFT JOIN
   (SELECT asset_id,id_number,MAX(id_eff_dt) AS id_eff_dt FROM govcorp..asset_ident WHERE id_cd='UAC' 
    GROUP BY asset_id,id_cd,id_number) c4 ON a.asset_id=c4.asset_id 
   LEFT JOIN
   (SELECT asset_id,id_number,MAX(id_eff_dt) AS id_eff_dt FROM govcorp..asset_ident WHERE id_cd='UAI'
    GROUP BY asset_id, id_cd, id_number) c5 ON a.asset_id = c5.asset_id

As you probably deduce asset_ident can have many rows for each type of ident. I only want the latest of these. To complicate matters sometimes there are more than one rows for a particular id_cd and asset_id where the dates are the same, I'd like to reduce the rows returned to one for these. asset_id is the primary key and I would like only one row per asset_id.

The above query does not do what I thought it would (I did not expect it to reduce to one row when there are same id_eff_dt for a given asset_id and id_cd). Is there are way of achieveing my desired result?

Thanks,

David

 
Sort by date Sort by votes
How can you tell which asset_ident is the latest? Is there a date field on the table you can use?

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Bad question. I mean is there a date field on

govcorp..asset a

that will match your max(id_eff_dt) from the other table.

If that is the case it will only be a matter of joining on

ID = ID AND DATE = DATE

If not, a more creative solution will be needed.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
No there is no date on asset table, nor should there be. The purpose of the id_eff_dt date is to show which is the latest id_number for a given ic_cd and asset_id. I only want the latest for each id_cd all in a single row per asset_id.

David
 
Upvote 0 Downvote
Can you lay out some sample data and your desired result? I think there is some miscommunication here, probably on my end.

Thanks,

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Ok,
Code: 
asset table
asset_id   party_long_name    party_short_name
1          null                GOO
2          DEEEEW              null
3          null                TREW

asset_ident
asset_id      id_cd    id_eff_dt    id_number
1              ISN     '20030812'   XYZ
1              ISN     '19990330'   RTY
1              CSP     '20010101'   EWQ
1              SED     '20000305'   FHUIO
2              SED     '19990909'   DFUIO
2              SED     '19990909'   RETWS
2              ISN     '20040509'   FKNY
3              ISN     '19990101'   GJDGJ
3              ISN     '20040308'   DJGGK
3              ISN     '20050606'   TRET
3              CSP     '20040909'   GJG
In the result from the above I'd like to see
Code: 
Cusip  Isin  Sedol Cusip144A Isin144A DESCRIPTION  ASSET_ID
EWQ    XYZ   FHUIO null      null     GOO          1
null   FKNY  DFUIO null      null     DEEEEW       2
GJG    TRET  null  null      null     TREW         3

I realise to get one of the 2 values for Asset_id = 2 and id_cd == ISN with identical id_eff_dt values might be difficult, I can deal with 2 rows for this in code if need be.

Thanks,

David
 
Upvote 0 Downvote
OK, It is getting clearer. Is there any particular reason you would want DFUIO to show in 'Sedol' rather than RETWS? If there is some kind of logic for how this is assigned that could be coded into your query.

Also, can you give an example of the query result you are getting now?

Bottom line - I think this can be done with a modification to your subqueries.

Thanks,

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Hi,

No reason for choosing between DFUIO rather than RETWS in 'Sedol'.

The result I am getting at the moment (since this is mock data and not the real stuff) is all rows. Here is some real data.

Code: 
Cusip	Isin	isin_id_eff_dt	Sedol	DESCRIPTION	ASSET_ID
001765AH9	<null>	<null>	<null>	AMR 8.63% 08/15/92	000019000000071e
008140AD7	US008140AD74	3/1/88 0:00	<null>	LION CONNECTICUT 8.63% 03/01/98	0000190000000f16
008140AD7	US008175AA91	12/14/98 0:00	<null>	LION CONNECTICUT 8.63% 03/01/98	0000190000000f16
008175AA9	US008140AD74	3/1/88 0:00	<null>	LION CONNECTICUT 8.63% 03/01/98	0000190000000f16
008175AA9	US008175AA91	12/14/98 0:00	<null>	LION CONNECTICUT 8.63% 03/01/98	0000190000000f16
001920AC1	US001920AC19	12/14/98 16:13	2579650	ARCO CHEM TECH 9.90% 11/01/00	0000190000000a13
001920AA5	<null>	<null>	<null>	ARCO CHEM TECH 9.35% 11/01/19	190000000811
001765AD8	<null>	<null>	<null>	AMR 8.63% 03/01/17	000019000000031a
008140AA3	<null>	<null>	<null>	AETNA 8.13% 10/15/07	0000190000000d14
008117AF0	US008175AE14	1/15/87 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
008117AF0	US536196AF59	2/19/01 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
008140AB1	US008175AE14	1/15/87 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
008140AB1	US536196AF59	2/19/01 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
008175AE1	US008175AE14	1/15/87 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
008175AE1	US536196AF59	2/19/01 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
536196AF5	US008175AE14	1/15/87 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000
536196AF5	US536196AF59	2/19/01 0:00	<null>	LION CONNECTICUT 8.00% 01/15/17	19000000000000000000000000

As you can see there are lots of repeats. I'd like to eliminate them. The choice between those with same asset_id, id_cd and id_eff_dt can be random, I still only want one row per asset_id.

Thx.

David
 
Upvote 0 Downvote
Wow, that is hard to read.

I think if you change your LEFT JOIN s all to INNER JOIN s that it will help to eliminate most of your duplicate rows.

You could also use max or min on your ID_Number in the subqueries to get those down to one row, but front end is probably the ideal place to tackle this because it will give you more flexibility in what to display.

Let me know how the inner joins work for you.

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Hi,

I cannot use INNER JOIN because some of the id_numbers have null. So if only one id_number is null then I get no information for that asset_id.

If I am using MAX(id_eff_dt) to get the latest then how do you have secondary condition for MIN(id_NUMBER for when there are 2 rows witht esame asset_id, id_eff_dt and id_cd.

Maybe it is not possible in SQL?

David
 
Upvote 0 Downvote
I just realized that. I'm working on something now that I think wil ldo the trick for you.

Alex


A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
You may try something like this (typed, untested):
...
FROM asset a
LEFT JOIN (
SELECT x.asset_id, MIN(x.id_number) AS id_number
FROM asset_ident x INNER JOIN (
SELECT asset_id, MAX(id_eff_dt) AS id_eff_dt FROM asset_ident
WHERE id_cd='ISN' GROUP BY asset_id
) y ON x.asset_id=y.asset_id AND x.id_eff_dt=y.id_eff_dt
WHERE x.id_cd='ISN' GROUP BY x.asset_id
) c1 ON a.asset_id=c1.asset_id
LEFT JOIN (
SELECT x.asset_id, MIN(x.id_number) AS id_number
FROM asset_ident x INNER JOIN (
SELECT asset_id, MAX(id_eff_dt) AS id_eff_dt FROM asset_ident
WHERE id_cd='CSP' GROUP BY asset_id
) y ON x.asset_id=y.asset_id AND x.id_eff_dt=y.id_eff_dt
WHERE x.id_cd='CSP' GROUP BY x.asset_id
) c2 ON a.asset_id=c2.asset_id
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Ok, unless I am mistaken (again...) I think this will get you the desired result (It did for me when I made tables of your sample data).

Note that I removed your bintostr(a.asset_id) AS ASSET_ID
as in SQL Server 2000 I don't have this function.

Also note the changes to your subqueries, that is really what gets the job done.

Let me know how this works out,

Alex

Code: 
SELECT 
 c2.id_number AS Cusip, c1.id_number AS Isin, c3.id_number AS Sedol, 
 c4.id_number AS Cusip144a, c5.id_number AS Isin144a, 
 ISNULL(a.party_short_name, a.party_long_name) AS DESCRIPTION,  
 (a.asset_id) AS ASSET_ID
FROM asset a LEFT JOIN 
   (SELECT x.asset_id, x.id_number FROM asset_ident x inner join
	(select asset_id, max(id_eff_dt) as id_eff_dt from asset_ident where id_cd = 'ISN' group by asset_id) y on 
	x.asset_id = y.asset_id
	and x.id_eff_dt = y.id_eff_dt WHERE id_cd='ISN') c1 ON a.asset_id=c1.asset_id
   LEFT JOIN
   (SELECT x.asset_id, x.id_number FROM asset_ident x inner join
	(select asset_id, max(id_eff_dt) as id_eff_dt from asset_ident where id_cd = 'CSP' group by asset_id) y on
	x.asset_id = y.asset_id
	and x.id_eff_dt = y.id_eff_dt WHERE id_cd='CSP') c2 ON a.asset_id=c2.asset_id
   LEFT JOIN
   (SELECT x.asset_id, x.id_number FROM asset_ident x inner join
	(select asset_id, max(id_eff_dt) as id_eff_dt from asset_ident where id_cd = 'SED' group by asset_id) y on
	x.asset_id = y.asset_id
	and x.id_eff_dt = y.id_eff_dt WHERE id_cd='SED')  c3 ON a.asset_id=c3.asset_id 
   LEFT JOIN
   (SELECT x.asset_id, x.id_number FROM asset_ident x inner join
	(select asset_id, max(id_eff_dt) as id_eff_dt from asset_ident where id_cd = 'UAC' group by asset_id) y on
	x.asset_id = y.asset_id
	and x.id_eff_dt = y.id_eff_dt WHERE id_cd='UAC')  c4 ON a.asset_id=c4.asset_id 
   LEFT JOIN
   (SELECT x.asset_id, x.id_number FROM asset_ident x inner join
	(select asset_id, max(id_eff_dt) as id_eff_dt from asset_ident where id_cd = 'UAI' group by asset_id) y on
	x.asset_id = y.asset_id
	and x.id_eff_dt = y.id_eff_dt WHERE id_cd='UAI')  c5 ON a.asset_id = c5.asset_id





A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Sometimes the hardest part is just understanding the problem. Glad you got it to work!

Alex

A wise man once said
"The only thing normal about database guys is their tables".
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
232
carp
carp
BobThornton
  • Locked
  • Question
Count records until a record value changes
Replies
2
Views
381
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
215
mmiah1982
mmiah1982
sal21
  • Locked
  • Question
SORT by part of filed 2
Replies
5
Views
232
2009luca
2009luca
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
193
carp
carp

Part and Inventory Search

Sponsor

Back
Top