adding the concordance to a collumn

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

General SQL Server Forums

New to SQL Server Programming

adding the concordance to a collumn

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2012-07-18 : 12:16:03

In the following code:
SELECT a.[BLS_io] iFMB

,imiters_io = case
when a.[BLS_io] = b.[fmb_io] then b.[miters_io]
end
,a.[iCom] cFMB

,cmiters_io = case
when a.[iCom] = b.[fmb_io] then b.[miters_io]
end
,a.[FoV]

FROM [BLS].[IO2012].[ACxC_1993] a
,[BLS].[IO2012].[io_concord_new] b

I am trying to add the concordance from the b table to the a table in a imiters_io and cmiters_io. b table is a 450 column which has all the items in a.icom and a.BLS_io.

But this code is adding more rows to the a table which is not wanted.

I am aware that in the b table for one fmb_io there are couple of miters_io:

fmb_io miters_io
1 5
1 6
1 7
2 3
2 4

But for me is enought to only take one of the miters_io(any) and not add more rows to a matrix.

Regards

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-18 : 12:21:57

You don't have a join condition here so will get a cartesian product

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 12:27:12

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.[BLS_io],a.[iCom] ORDER BY (SELECT 1)) AS rn,
,a.[BLS_io] iFMB

,imiters_io = b1.[miters_io]
,a.[iCom] cFMB

,cmiters_io = b2.[miters_io]
,a.[FoV]

FROM [BLS].[IO2012].[ACxC_1993] a
left join [BLS].[IO2012].[io_concord_new] b1
on a.[BLS_io] = b1.[fmb_io]
left join ,[BLS].[IO2012].[io_concord_new] b2
on a.[iCom] = b2.[fmb_io]
WHERE b1.[fmb_io] is not null
or b2.[fmb_io] is not null
)t
WHERE Rn=1
[/code]

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

goligol
Posting Yak Master

128 Posts

Posted - 2012-07-18 : 12:49:56

hello,
it is not working in my case, I was thinking if I have a one by one concordance in b(following) table I can figure out the rest:

SELECT [fmb_io]
,[miters_io]
FROM [BLS].[IO2012].[io_concord_new]
order by fmb_io

fmb_io miters_io
1 111335
1 1111A0
1 111400
1 1119A0
1 111910
2 112100
2 112100
2 112300
2 112A00
3 113A00
4 113300
5 114100
5 114200
6 115000
7 211000
8 212100
9 212210
9 2122A0

this to be computed as:

fmb_io miters_io
1 111335
2 112100
3 113A00
4 113300
5 114100
6 115000
7 211000
8 212100
9 212210

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources