Selecting values cross table

[FatMooseHenry]

Hello, i've got two tabels, "Artists" and "Music". The relevant columns are:

Artists:
[Id],[Name]

Music:
[Id],[ArtistId], [Title],[Added]

I want select the post's in the music table, but instead of returning the artist id, it should return [Name] from the Artist table where Music.ArtistId = Artists.Id (so it looks in the Artists table and selects the right name for the id)

it should be simple, but i cant make it work. (im not good with subqueryies). And i have only come up with solutions that doesnt select the right posts

[campkev]

this should do it
select artist.name, music.* from
music
inner join artist on music.artistid = artist.id

[FatMooseHenry]

Hey. Thanks for the reply, and im sorry for the huge delay.
Just to make it more complicated, is it possible, to select another column if the ArtistId=-1?
Like, if the ArtistId!=-1, make the inner join, and if ArtistId==-1, do something like the Case operator and then choose the column [Made_By], instead of the Name?

[campkev]

not sure I understand what you want but try this

select artist.name, music.* from 
music 
inner join artist on artist.id = case when
music.artistid > -1 then music.artistid
else music.madeby
end

[campkev]

actually, after looking at it further, I'm not sure that is what you want. What table is "madeby" part of and how does it relate to the other two tables?

[FatMooseHenry]

Not quite...i will keep trying my self, but this may help understanding my problem:

select Top 3 Music.Id, Music.Title,
CASE WHEN Music.ArtistId=-1 THEN
Music.MadeBy
ELSE
Artists.Name
END AS [Name] from Music CASE WHEN Music.ArtistId!=-1 THEN inner join Artists on Music.ArtistId = Artists.Id END
where Music.FrontPage=1 AND Music.SiteId=3 Order by Music.Added DESC

[FatMooseHenry]

Maby this is better:

select Top 3 Music.Id, Music.Title,
CASE WHEN Music.ArtistId=-1 THEN
Music.MadeBy AS [Name] from Music
ELSE
Artists.Name from Music inner join Artists on Music.ArtistId = Artists.Id
END where Music.FrontPage=1 AND Music.SiteId=3 Order by Music.Added DESC

This doesnt work, but you may get the idea of what i would like it to do