Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shawnmolloy
Yak Posting Veteran
93 Posts |
 Posted - 2007-11-02 : 20:21:57
|
Hello,I have this table called "Playlist_Items". It has different "media types" in it. An MP3 is "1", a video is "2", etc.I want to make a query that gets all the items in the playlist and gets common properties between them, like the name, user, etc.The only problem is that the tables aren't normalized, so I have to do something like, if the "MediaType" is 1 then look in the MP3 table and get this column, otherwise if its 2 then look in the video table. Etc.I have no idea how to do this in SQL... :/ Here is a diagram of my tables: Thanks very much! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-11-02 : 20:51:51
|
| select <fields>from playlistitems ijoin video von v.videoid = i.mediaitemidUNIONselect <fields>from playlistitems ijoin MP3 mon m.mp3id = i.mediaitemiddont need joins if u arent selecting any fields from video or mp3 tables, in which case u dont need union either -- just a case statementselect case mediatypewhen 1 then 'video'when 2 then 'mp3'else 'something else' end as [media type]from playlist_items... |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
arorarahul.0688
Posting Yak Master
125 Posts |
Posted - 2007-11-03 : 05:54:15
|
| try thisdeclare @mediaid <type>select @mediaid from medialist where <cond>if exsist (@mediaid=select id from video )beginselect < field> from medialist ml join video v on ml.id=v.idendelsebeginselect<fields> from medialist ml join mp3 mp on mp3.id=ml.idendendRahul Arora MCA 07 BatchNCCE Israna, PanipatHRY, INDIA######################IMPOSSIBLE = I+M+POSSIBLE |
|
|
|
|
|
|
|
|
Join question - if then or else in a join?