Selecting only the first result from an inner join

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)

Selecting only the first result from an inner join

Author

Topic

outspaced
Starting Member

21 Posts

Posted - 2007-04-05 : 07:56:04

Hi,

I'd like to do a query to select the GUIDs for products (products are music albums) that are linked to a track that is in wav format. Problem is that doing the join to the track table means I get the product GUID repeated as many times as there are tracks on the release.

I am unable to use DISTINCT as this is being used for an INSERT statement, and requires the use of newid().

Here is my select query:

select p.guid, newid(), 'wav', .000, .000, NULL, NULL, 'A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6', 1.6936, .0000
from tblproductdigital p
inner join tbltitledigital t on p.tbltitledigitalguid = t.guid
inner join tbllabeldigital l on t.tbllabeldigitalguid = l.guid
inner join tbltrackdigital tr on tr.tblproductdigitalguid = p.guid
inner join tbltrackdigitalstorage tds on tr.guid = tds.tbltrackdigitalguid
where tds.trackformat='wav'
and l.tbldigitaldistributorguid = '50C2E8B7-3B4A-56D2-BB71-D8BB59CE4353'

result set looks like this:
5F28EBC8-D284-10B1-56A7-00A33A0965DC C87C9DB7-AB42-4094-BB61-4B8054CC8F45 wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000
5F28EBC8-D284-10B1-56A7-00A33A0965DC 97EAE23E-7B15-451C-BCA9-392C3F192E70 wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000
5F28EBC8-D284-10B1-56A7-00A33A0965DC DA692ECC-7568-4990-84C2-E8C29639B5DC wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000
BCDF4CD9-BFEA-5642-90E7-055145BB8AF7 6164BD94-4ED2-4F47-9E09-F9B66E9EE789 wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000
BCDF4CD9-BFEA-5642-90E7-055145BB8AF7 A78DDEBC-222F-4164-A133-9B3FF9D64F02 wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000
BCDF4CD9-BFEA-5642-90E7-055145BB8AF7 218F1386-BF15-4680-8A79-60EC25C0EF94 wav 0.000 0.000 NULL NULL A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6 1.6936 0.0000

.... etc etc ....

... any ideas?

cheers,
Alex

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-04-05 : 08:01:23

what is the primary key of the table tblproductdigital ???

How about somthing like this ??



select p.guid, newid(), 'wav', .000, .000, NULL, NULL, 'A24A6AC8-A21D-49B9-AAF8-E0C89738DEB6', 1.6936, .0000
from tblproductdigital p
inner join tbltitledigital t on p.tbltitledigitalguid = t.guid
inner join tbllabeldigital l on t.tbllabeldigitalguid = l.guid
inner join tbltrackdigital tr on tr.tblproductdigitalguid = p.guid
inner join tbltrackdigitalstorage tds on tr.guid = tds.tbltrackdigitalguid
where tds.trackformat='wav'
and l.tbldigitaldistributorguid = '50C2E8B7-3B4A-56D2-BB71-D8BB59CE4353'
And 
p.PK = 
(
	Select Max(d.Pk) From tblproductdigital d Where d.guid = p.guid 

)

Chirag

http://chirikworld.blogspot.com/

outspaced
Starting Member

21 Posts

Posted - 2007-04-05 : 08:21:30

Thanks for the response.

The problem I see with that is that it is selecting the same record from tblProductDigital, so that match you suggest will always occur.

However, you did inspire to look at the problem differently, and I realised that it will work if I add tr.tracknumber = 1 ... and I did kick myself when I realised.

Thanks for your help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources