MS SQL: Select over several tables / conditions
===

Okay, I'm relatively new to the more advanced uses of SQL, MSSQL in this current problem.

I have several tables that I need to gather informations from, and several of these tables links to other tables where I need a specific information. As a result, I just want one row with all the information, preferential named with aliases.

For example:

Tab_Transcoders: ID, VideoCamID, InputStreamID, OutputStreamID.
where InputStream links to another table where I need the row of the matching ID, where in this row are other ID's (e.g. StreamType_ID that belongs to a third table containing ID_StreamType and Description etc.)
Same with OutputStreamID, same with VideoCamID.

In the end, I need a row containing for example:
ID, VideoCamID, InputStreamID, InputStreamType, InputStreamTypeDesc, OutputStreamID, OutputStreamType, OutputStreamDesc, VideoCamID, etc. etc. etc.

It is important for me that I can set aliases, as for example InputStreamID & OutputStreamID links to the same table where all my Streams are listed (with IP's, Descs..)

I can accomplish this with doing like 100 SELECTS & SUBSELECTS, but I don't think that's an appropriate way.
I read some informations about things like CURSOR, UNION, FETCH, JOIN etc. etc.. but I don't know which one I have to use for my purpose.


I hope you can help.

Regards,
eli