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

    Retrive the names in the country,state,cites,state,cites,Country,state,cites by passing record id

    Hi have the following table structure.

    RecordTable
    =========
    rid int identity primarykey
    Name nvarchar(250)

    Country
    ======
    ContryId int identity primarykey
    Name nvarchar(250)
    rid forenkey to recordTable

    State
    ======
    stateId int identity primarykey
    Name nvarchar(250)
    ContryId forenkey to countryTable

    City
    ======
    cityid int identity primarykey
    Name nvarchar(250)
    stateId forenkey to countryTable

    Now I want retrieve all the name of country,state,cites,state,cites,Country,state,cites by passing rid

    The data will be like this
    Names
    ==============
    Country1
    State1
    city1
    city2
    city3
    State2
    city1
    city2
    city3
    Country2
    State1
    city1
    city2
    city3
    Country3
    State1
    city1
    State2
    city1
    State3
    city1

    Any one please give the union query to retrieve the data.

    Regards,
    Koti.
  • #766525
    Hi

    you can go through Below Query

    Table Script




    create table RecordTable
    (
    rid int identity primary key,
    Name nvarchar(250)
    )

    create table Country
    (
    ContryId int identity primary key,
    Name nvarchar(250),
    rid int
    )

    create table State
    (
    stateId int identity primary key,
    Name nvarchar(250),
    ContryId int
    )

    create table City
    (
    cityid int identity primary key,
    Name nvarchar(250),
    stateId int
    )


    Query Code



    Select b.Name from RecordTable a,Country b where a.rid=b.rid -- Country
    union all
    Select a.Name from State a,Country b where a.ContryId=b.ContryId -- State
    union all
    Select a.Name from City a,State b where a.stateId=b.stateId --City


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766532
    There are many way to do this based on your requirement.

    Create Country, State,City objects.

    WAY 1

    Create the instance List<County> MyCountry = new List<Country>()
    Note : You have to create objects such as one country has multiple states and one state has multiple cities.

    a. You can mak DB call to get all the countrys
    b. Itrate the country list and make State list DB can and add the states into the country
    c. Itrate the States and make Cities DB call and add it in the corresponding states.

    WAY 2

    Make DB call for getting complete list using the query as bellow

    Select * from City
    inner join state on state.cityid = city.id
    inner join country on country.stateid = state.id
    order by country.name,state.name,city.name

    Now you can iterate the list and add populate the country list using while loop.
    INDIA, KARNADAKA, BANGALORE
    INDIA, KARNADAKA, MYSORE
    ...
    ...
    The list will be in the order so your can add one country. under that add the state and city. Use some boolean flags to handle it in your while loop.

    By Nathan
    Direction is important than speed

  • #766540
    Hi,

    You need to understand the structure before wrote code for that,

    1) based on record id you are fetching country details, i.e. you have to pass rid as input parameter,

    2) based on country you are fetching state details, i.e. you have to pass countryid as input parameter.

    3) based on state you are fetching city details,i.e. you have to pass stateid as input parameter.

    now you just make a code to follow all above points,


    Protected void BindCountry()
    {
    DataSet dsCtry= //get data from database and pass record id as input parameter.
    ddlCtry.DataSource=dsCtry;
    ddlCtry.DataTextField="CountryName";
    ddlCtry.DataValueField="CountryId";
    ddlCtry.DataBind();
    }


    based on country dropdown selection bind state details

    Protected void ddlCtry_OnSelectedIndexChanged(object sender, EventArgs e)
    {
    DataSet dsState=//pass country id as input parameter and fetch data from database.
    ddlState.DataSource=dsState;
    ddlState.DataTextField="StateName";
    ddlState.DataValueField="StateId";
    ddlState.DataBind();
    }


    In the same manner you should bind city details by passing state id as a input parameter.

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

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

  • #766910
    You can fetch all the table value once and store it session or cache and then filter it according to the selection.So that it minimize the calls to database here is the code

    Dataset.Select( filter string).

    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766922
    Hi,
    There are many ways to achieve this. Based on Union and Union All and Joins and so on. Here with I enclosed with example,

    Step 1:
    Union All Provides duplicates too.. If both tables having the same records means it will provide you as Result.
    Select b.Name from RecordTable a,Country b where a.rid=b.rid
    union all
    Select a.Name from State a,Country b where a.ContryId=b.ContryId
    union all
    Select a.Name from City a,State b where a.stateId=b.stateId

    Step 2:
    To Union All keyword problems you can use Union means it provides the results with out duplicate.
    Select b.Name from RecordTable a,Country b where a.rid=b.rid
    union
    Select a.Name from State a,Country b where a.ContryId=b.ContryId
    union
    Select a.Name from City a,State b where a.stateId=b.stateId

    Step 3:
    Another way is Using Joins in Query.
    Select b.Name from RecordTable a join Country b on a.rid=b.rid
    Inner Join Country c on a.ContryId=.ContryId
    Inner Join City on a.stateId=c.stateId

    Regards,
    Karunanidhi.K


Sign In to post your comments