how to get columns associated to a Sharepoint List through Lookup columns in its view

i have two tables in sharepoint 2010. one is of departments which contains department name and its location. the other list is of Employee which is associated with department through lookup fields on department n开发者_运维问答ame.

now i want to create a view of employee list which contains columns of both employee list and department list. but default view creation functionality of sharepoint 2010 doesnt show the columns of department list while creating a view of employee list

can any ony please help me out

In the settings lookup field, select required fields of department list, and then add these fields to employee list view.

 SPFieldLookupValue value=new SpFiledlookupvalue(item["column name"]);
    string id=value.lookupid;//you can retrieve the text,id
    string text=value.lookuptext;

    SPList departmentList=new oweb.Lists["Department List"];
    SPQuery getDepartment=new SPQuery( )
    getDepartment.Query=  "<Where>  <Eq>  <FieldRef Name='ID' />
                                                  <Value Type='Counter'>id</Value>
                                                 </Eq>
                                        </Where>";
    SPListItemCollection departMentRow=departmentList.GetItems(getDepartment)

You can check this code for your reference....and query like what you want..