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

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

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?

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

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?

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.