You must Sign In to post a response.
  • Category: SQL Server

    How to retrive the data from child table based upon upon the ID from Master table


    Are you looking for a way to retrive the data from child table based upon upon the ID from Master table ? then read this thread to know more about it



    hi all,

    I have 2 tables
    Menu Table - >It is a Master table
    SubModule Table -> It is a child table

    Menu Table columns are MenuID, MenuName
    SubModule Table columns are ModuleID,SubModuleName,MenuID

    Now I should bind the data to the menu tab,If i click on MenuName then related subModuleName should be displayed based upon the MenuID

    Before I am using in a single table it is working fine,but my requirement is to use 2 tables.
    here is the code which i have done
    private void GetMenuData()
    {
    DataTable table = new DataTable();
    DataSet ds = new DataSet();
    string strCon = System.Configuration.ConfigurationManager.ConnectionStrings
    ["Employee"].ConnectionString;
    SqlConnection conn = new SqlConnection(strCon);
    string sql = "select menu_id, menu_name, menu_parent_id, menu_url from menuMaster";
    SqlCommand cmd = new SqlCommand(sql, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    da.Fill(table);

    DataView view = new DataView(table);
    view.RowFilter = "menu_parent_id is NULL";
    foreach (DataRowView row in view)
    {
    MenuItem menuItem = new MenuItem(row["menu_name"].ToString(),
    row["menu_id"].ToString());
    menuItem.NavigateUrl = row["menu_url"].ToString();
    menuBar.Items.Add(menuItem);
    AddChildItems(table, menuItem);
    }
    }
    private void AddChildItems(DataTable table, MenuItem menuItem)
    {
    DataView viewItem = new DataView(table);
    viewItem.RowFilter = "menu_parent_id=" + menuItem.Value;
    foreach (DataRowView childView in viewItem)
    {
    MenuItem childItem = new MenuItem(childView["menu_name"].ToString(),
    childView["menu_id"].ToString());
    childItem.NavigateUrl = childView["menu_url"].ToString();
    menuItem.ChildItems.Add(childItem);
    AddChildItems(table, childItem);
    }
    }

    Thanks in advance
  • #756563
    Hi,

    Use the below sql script to retrive the data from two tables.

    SELECT MT.menuid,
    MT.menuname,
    SM.moduleid,
    SM.submodulename
    FROM menutable MT
    JOIN submodule SM
    ON MT.menuid = SM.menuid

  • #756566
    Hi,

    First off all understood the JOINs concept then start workout this requirement that will give you an idea how to implement this.

    you need to join tables like below.


    select col1,col2...
    from mastertable m,
    childtable c
    where m.masterid=c.masterid


    In above example first I called master table then I'm calling child table, if you call in the same manner the performance of the application will improve.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #756567
    Hi,
    Try to debug the code under AddChildItems function. Just see the values you are getting under DataView viewItem and also in below code
    viewItem.RowFilter = "menu_parent_id=" + menuItem.Value;

    You might find an issue here to resolve your issue.


    Regards,
    Asheej T K

  • #756651
    Now what exact problem you are getting ? If it is the problem from SQL then use like this -

    SELECT m.MENUNAME, sm.MODULENAME,
    FROM MENU m
    INNER JOIN SUBMODULE sm ON m.MENUID =
    sm.MENUID

    Hope it helps.

    Thanks & Regards,
    Abhijith

    Mail - abhijith.pn@gmail.com
    Check My Blog - http://www.solvemytechissue.in/

  • #756672
    Thank youall issue is fixed

  • #757320
    You can use subquery :
    select submodulename from submodule where menuid = (select menuid from menu where menuname = 'MM')

  • #757567
    Hi,

    while forming the menu you should check for the sub-menu items too,

    like i am creating menu so while creating first tab ill check for the sub node available for that or not if then ill retrieve the sub menu list and then add to that node in the sub menu position then i will go for next like this need to verify the construction of the menu up to end. because menu completely works based up on order of items and css style you should be little bit care full while writing this kind of things.


  • Sign In to post your comments