0

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

2
Contributors
6
Replies
7
Views
11 Years
Discussion Span
Last Post by FatMooseHenry
0

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?

0

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
0

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?

0

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

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.