Joining a table to a query - SQL Server Forums

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

Joining a table to a query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-10 : 13:51:00

How do I join this table: T2Dib so I can add the field lorec to the output (lorec is in the T2Dib table)

to this query?


SELECT area, doc, [type], convert(char,app_rcpdt, 101) as app_rcpdt, location, clm
FROM
(
select area,doc, [type], convert(char,app_rcpdt, 101) as app_rcpdt,maxseqnum,location,clm,row_number() over(partition by area,doc, type, app_rcpdt,clm order by maxseqnum desc) as rown

from T2pendingcos
) t1
WHERE rown = 1 and type='1' and doc='094'--@doc
ORDER BY area,maxseqnum

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-10 : 15:52:57

It would be something like this. Not sure what the join columns would be - that would depend on your business logic:

SELECT
	area,
	doc,
	[type],
	CONVERT(CHAR, app_rcpdt, 101) AS app_rcpdt,
	location,
	clm,
	t2.lorec
FROM
	(
	    SELECT
	    	area,
	    	doc,
	    	[type],
	    	CONVERT(CHAR, app_rcpdt, 101) AS app_rcpdt,
	    	maxseqnum,
	    	location,
	    	clm,
	    	ROW_NUMBER() OVER(
	    	    PARTITION BY area,
	    	    doc,
	    	    TYPE,
	    	    app_rcpdt,
	    	    clm ORDER BY maxseqnum DESC
	    	) AS rown
	    FROM
	    	T2pendingcos
	) t1
	INNER JOIN T2Dib t2 ON t2.JoinColumn = t1.JoinColumn
WHERE
	rown = 1
	AND TYPE = '1'
	AND doc = '094'--@doc
ORDER BY
	area,
	maxseqnum

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-10 : 16:16:50

Thanks the joined columns are clm

When I ran what you wrote I got:

Msg 209, Level 16, State 1, Line 33
Ambiguous column name 'doc'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'doc'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'app_rcpdt'.
Msg 209, Level 16, State 1, Line 7
Ambiguous column name 'clm'.

So I added the alias to it (items are in bold)


SELECT
	area,
	doc,	[type],
	CONVERT(CHAR, app_rcpdt, 101) AS app_rcpdt,	location,
	clm,	t2.lorec
FROM
	(
	    SELECT
	    	area,
	    	doc,
	    	[type],
	    	CONVERT(CHAR, app_rcpdt, 101) AS app_rcpdt,
	    	maxseqnum,
	    	location,
	    	clmssn,
	    	ROW_NUMBER() OVER(
	    	    PARTITION BY area,
	    	    doc,
	    	    TYPE,
	    	    app_rcpdt,
	    	    clmssn ORDER BY maxseqnum DESC
	    	) AS rown
	    FROM
	    	T2pendingcossn
	) t1
	INNER JOIN T2Dibpend t2 ON t2.clmssn = t1.clmssn
WHERE
	rown = 1
	AND TYPE = '1'
	AND doc = '094'--@docORDER BY
	area,
	maxseqnum

These are the results. It's coming out in fours. I should only have two records (look at the clm field)


Area   doc    type    app_rcpdt                location         clm   lorec
02	094	1	12/07/2009                    	FO	54915	094
02	094	1	12/07/2009                    	FO	54915	094
02	094	1	12/07/2009                    	FO	54915	094
02	094	1	12/07/2009                    	FO	54915	094
02	094	1	12/30/2009                    	FO	21884	094
02	094	1	12/30/2009                    	FO	21884	094
02	094	1	12/30/2009                    	FO	21884	094
02	094	1	12/30/2009                    	FO	21884	094

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-08-10 : 16:28:35

I got it to work. I got rid of the row partition and made clm distinct so I'm getting the same results when I didn't join the tables. Thanks for your help!


SELECT distinct(t1.clm) as clm,t1.area, t1.doc, t1.[type], convert(char,t1.app_rcpdt, 101) as app_rcpdt, t1.location,  t2.lorec
from T2pendingcos t1
INNER JOIN T2Dib t2 ON t2.clm = t1.clm
WHERE  type='2' and t1.doc='094'
order by clm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources