Complicated SQL Join Statement

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)

Complicated SQL Join Statement

Author

Topic

mkswanson
Starting Member

21 Posts

Posted - 2009-09-03 : 16:22:35

I have two tables that contain different pieces of information. Table B has multiple entries which coorespond to a single entry in Table A.

Table A:

SiteID
SiteName
(And many more columns)

Table B:
EntryID
SiteID (Same as TableA.SiteID)
Data1
Data2
(And many more columns)

I need to create a statement that will join the SiteName from Table A with the entry with the largest EntryID and the same SiteID in Table B.

So far, I have:

SELECT A.SiteName, B.Data1, B.Data2
FROM A JOIN B
ON A.SiteID = B.SiteID

This produces:

Customer 1, Data 1, Data 2
Customer 1, Data 1, Data 2
Customer 1, Data 1, Data 2
Customer 1, Data 1, Data 2
Customer 1, Data 1, Data 2
Customer 1, Data 1, Data 2
Customer 2, Data 1, Data 2
Customer 2, Data 1, Data 2
Customer 2, Data 1, Data 2
Customer 2, Data 1, Data 2
Customer 2, Data 1, Data 2

I need to get this so that only the results with the largest EntryID are returned for each customer. I've tried several things, but can't get anything that works.

Pleas help...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-03 : 16:32:28

[code]SELECT A.SiteName, B.Data1, B.Data2
FROM A INNER JOIN (Select row_number() over(partition by SiteID order by EntryID desc) as seq, Data1,Data2,SiteID,EntryID) B
ON A.SiteID = B.SiteID
WHERE B.seq = 1[/code]

mkswanson
Starting Member

21 Posts

Posted - 2009-09-04 : 09:38:20

Thanks for the help! I will try this out this morning.

mkswanson
Starting Member

21 Posts

Posted - 2009-09-04 : 12:43:38

I got a chance to try to work with this for a while, and I am having problems. I keep getting the error: "The multi-part identifier [column name] could not be bound for all of the columns listed in the select statement. I've tried several variations, but I just can't seem to get this query right. Any help would be appreciated!

Currently, I have the following:

SELECT ENTITY_DATA.CustomerName, POLLING_DATA.ProductLine,
POLLING_DATA.ProductVersion
FROM ENTITY_DATA INNER JOIN (SELECT row_number() over(partition by POLLING_DATA.EntityID order by POLLING_DATA.PollingID desc) as seq, POLLING_DATA.ProductLine, POLLING_DATA.ProductVersion, POLLING_DATA.EntityID, POLLING_DATA.PollingID) POLLING_DATA
ON ENTITY_DATA.EntityID = POLLING_DATA.EntityID
WHERE seq = 1

What am I missing?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 13:50:27

You are missing the FROM clause in the subquery

try this..

SELECT a.CustomerName, b.ProductLine, 
b.ProductVersion
FROM ENTITY_DATA a INNER JOIN 
(SELECT row_number() over(partition by EntityID order by PollingID desc) as seq, ProductLine, ProductVersion, EntityID,PollingID FROM POLLING_DATA) b
ON a.EntityID = b.EntityID
WHERE b.seq = 1

mkswanson
Starting Member

21 Posts

Posted - 2009-09-04 : 13:59:26

That did it...thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-04 : 14:02:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources