InnerJoning two tables statements

Question

InnerJoning two tables statements

allybfg

Grasshopper

Points: 22
More actions
August 27, 2008 at 5:50 pm

#197586

Hi Guys
I am trying to join two tables so that i can bind it to my datalist
I have two tables Albums and Resimler
This is what i can do but doesn't give me a results i wanted since it repeats AlbumID and i needed only one 1 AlbumID
SELECT Albums.AlbumID, Albums.AlbumName, Albums.PostedDate, Albums.Views, Resimler.ResimId, Resimler.AlbumID AS Expr1,
Resimler.Views AS Expr2
FROM Albums INNER JOIN
Resimler ON Albums.AlbumID = Resimler.AlbumID
ORDER BY Albums.PostedDate DESC, Expr2 DESC
what i wanted to do is to select Top 1 ResimId from Resimler table and join it to Album table so results will have unique Albums.AlbumID, Albums.AlbumName, Albums.PostedDate, Albums.Views, Resimler.ResimId, without repeating AlbumID
how can i do it
Please help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

SELECT Albums.AlbumID, Albums.AlbumName, Albums.PostedDate, Albums.Views, Resimler.ResimId, Resimler.AlbumID AS Expr1,

Resimler.Views AS Expr2

FROM Albums INNER JOIN

(Select TOP 1 * From Resimler R Where Albums.AlbumID = R.AlbumID) Resimler

ON Albums.AlbumID = Resimler.AlbumID

ORDER BY Albums.PostedDate DESC, Expr2 DESC

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

allybfg Grasshopper Points: 22 More actions · Answer 2

Thanks for your reply

This return only one row with only one AlbumID and one ResimId and this is not what i want.

I want table to return All AlbumID from Albums together with selected top 1 ResimId from the table Resimler and by which Album.AlbumID = Resimler.ResimId

thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Well, maybe try this:

SELECT Albums.AlbumID, Albums.AlbumName, Albums.PostedDate, Albums.Views, Resimler.ResimId, Resimler.AlbumID AS Expr1,

Resimler.Views AS Expr2

FROM Albums CROSS APPLY

(Select TOP 1 * From Resimler R Where Albums.AlbumID = R.AlbumID) Resimler

ORDER BY Albums.PostedDate DESC, Expr2 DESC

If that doesn't work, then I will need to see the Table definitions and some sample data. Here is a link that explains how to provide this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]