May 4, 2013 at 1:27 pm
Hello,
I have two tables, one call BoatDetails and the other BoatImages there both linked by the BOATID
when i do a select to return all the Boats + the Boat Images associated with that boat i get duplicate rows.
This is because one boat could have 1 - 20 images associated with it, Is there a way I can just return the 1 boat + 1 image associated with it?
This is my SQL query im not sure how or where i could achieve the above statement?
Select Distinct b.BoatID, b.Title, b.YearOfRegistration as Registration,
case b.Condition
when 1 then'Excellent'
when 2 then 'Average'
when 3 then 'Needs some TLC'
end as Condition,
b.[Length], '£' + CAST(Convert(Decimal(10,2),b.Price) as nvarchar) as Price, bl.[Image]
From BoatDetails as b left join BoatImages bl on b.BoatID = bl.BoatID
where b.IsArchived = 0
So just to clarify i want to return 1 boat + 1 image not 2 or 3 of the same boat just because there is more then one image, can some one help me
May 4, 2013 at 2:41 pm
Can any one help me with the above or point me in the right direction to help me resolve this problem.
May 4, 2013 at 3:02 pm
Select Distinct
b.BoatID,
b.Title,
b.YearOfRegistration as Registration,
case b.Condition
when 1 then'Excellent'
when 2 then 'Average'
when 3 then 'Needs some TLC'
end
as Condition,
b.[Length],
'£' + CAST(Convert(Decimal(10,2),b.Price) as nvarchar) as Price,
--> change this bl.[Image] to
MAX(bl.[Image]) OVER (PARTITION BY bl.DateCreated) AS [Image]
From
BoatDetails as b
left join
BoatImages bl
on b.BoatID = bl.BoatID
where
b.IsArchived = 0
--or change it to a related subquery
Select Distinct
b.BoatID,
b.Title,
b.YearOfRegistration as Registration,
case b.Condition
when 1 then'Excellent'
when 2 then 'Average'
when 3 then 'Needs some TLC'
end
as Condition,
b.[Length],
'£' + CAST(Convert(Decimal(10,2),b.Price) as nvarchar) as Price,
--> change this bl.[Image] to
(SELECT TOP(1) [Image] FROM BoatImages WHERE BoatID = b.BoatID ORDER BY ImageID DESC) AS [Image]
From
BoatDetails as b
where
b.IsArchived = 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply