Filter data using join - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Filter data using join

Author

Topic

ra_sasi
Starting Member

9 Posts

Posted - 2005-12-22 : 13:56:04

Hi,
I am new to this forum and i hope this is going to be a good experience. While writing a particualr SQL, am facing this problem

TABLE Resource

Res_Id(pk) Name
1 Res1
2 Res2
3 Res3

TABLE Upload

Upload_Id(pk) ResId(fk) FILE_PATH
1 1 PATH1
2 1 PATH2
3 1 PATH3
4 2 PATH4
5 2 PATH5
6 2 PATH6

Our requirement is to get the maximum UploadId and the corresponding file path for all the resource Id

Expected output
Res_id Upload_Id FILE_PATH
1 3 PATH3
2 6 PATH6

If I am not putting FILE_PATH in SQL,its working fine and is returning Res_id and Upload_id

select RM.RES_ID,max(UPD.UPLOAD_ID),RM.name
from Resource RM join Upload as UPD
ON RM.resource_id = UPD.resource_id
group by rm.RES_ID,UPD.RES_ID, RM.Name
having RM.RESOURCE_ID=UPD.RESOURCE_ID;

But if I am adding FILE_PATH in SQL, its returning multiple rows

select RM.RES_ID,max(UPD.UPLOAD_ID),RM.name,UPD.FILE_PATH
from Resource RM join Upload as UPD
ON RM.resource_id = UPD.resource_id
group by rm.RES_ID,UPD.RES_ID, RM.Name,UPD.FILE_PATH
having RM.RESOURCE_ID=UPD.RESOURCE_ID;

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-22 : 14:26:28

Well I certainly hope your experience here is fun, educational, and spirtually uplifting. Welcome!


use pubs
go
create TABLE Resource (Resource_id int primary key, Name varchar(10))
go
insert Resource 
select 1, 'Res1' union
select 2, 'Res2' union
select 3, 'Res3'

go
create TABLE Upload 
	(Upload_Id int primary key
	,Resource_Id int references Resource(Resource_id)
	,FILE_PATH varchar(10))
go
insert Upload
select 1, 1, 'PATH1' union
select 2, 1, 'PATH2' union
select 3, 1, 'PATH3' union
select 4, 2, 'PATH4' union
select 5, 2, 'PATH5' union
select 6, 2, 'PATH6'
go
select	a.Resource_id
	,a.upload_id
	,b.File_Path
from	(--derived table to get the max upload_id for each resource_id
	select	resource_id
		,max(upload_id) upload_id
	from	Upload
	group by resource_id
	) a
join	Upload b
		on b.upload_id = a.upload_id

go
drop table Upload
drop table Resource

EDIT:

quote:
select RM.RES_ID,max(UPD.UPLOAD_ID),RM.name,UPD.FILE_PATH
from Resource RM join Upload as UPD
ON RM.resource_id = UPD.resource_id
group by rm.RES_ID,UPD.RES_ID, RM.Name,UPD.FILE_PATH
having RM.RESOURCE_ID=UPD.RESOURCE_ID;

Your HAVING clause is not needed because it simply duplicates your join criteria.

By adding File_path to the GROUP BY clause, you've now instructed sql server to make sure each File_Path is represented by a group.

UPD.RES_ID is not needed in your group by clause because based on your join criteria it will always be the same as rs.res_id, and it's not included in your select list.

Be One with the Optimizer
TG

ra_sasi
Starting Member

9 Posts

Posted - 2005-12-22 : 15:00:38

Thanks TG...its wonderfult o get reply in such a short time and it WORKS!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-23 : 00:49:36

Or

Select Resource_id,Upload_Id,File_Path from Upload U
where upload_id=(Select max(upload_id) from Upload where Resource_id=U.Resource_id group by Resource_id)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources