SQL Server Performance

Extracting data from four tables

Discussion in 'SQL Server 2008 General Developer Questions' started by amit1902, Jun 21, 2011.

  1. amit1902 New Member

    Hi,
    I am just struct with a query generation to extract data from 4 table. Can any one suggest me to do this -
    Here is the overview of problem-
    Sec_table
    ac_no, part_no, sec_name, urban_rural, postoff_id, district_id,vlocation,town_id

    village_table
    vlocation,village_name

    postoff_table
    post_off_id,postoff_pin

    town_table
    district_id,town_id,town_name

    I need a query which gives me -

    sec_table.sec_name, if( sec_table.urban_rural='U') then
    {
    town_table.town_name where sec_table.district_id=town_table.district_id
    and sec_table.town_id=town.town_id
    }
    else ( sec_table.urban_rural='R') then
    {
    sec_table.vlocation=village.vlocation
    }
    as location, postoff.postoff_pin
    from sec_table, village_table, town_table, postoff_table
    where sec_table.ac_no=62 and sec_detail.part_no=69
  2. amit1902 New Member

    I have solved it myself. If any one faces same problem that this may help him. Here's the query -

    select distinct sec_detail.section_name_en,sec_detail.section_name_v1,NNN.NNN_name_en as location,
    NNN.NNN_name_v1 as location_v,post_off.postoffpin
    from sec_detail,nnn,post_off
    where sec_detail.district_id=nnn.district_id
    and sec_detail.nnn_id=nnn.nnn_id
    and sec_detail.ac_no=62
    and sec_detail.part_no=69
    and sec_detail.postoff_id=post_off.postoff_id
    UNION
    select distinct sec_detail.section_name_en,sec_detail.section_name_v1,villages.village_name_en as location,
    villages.village_name_v1 as location_v,post_off.postoffpin
    from sec_detail,villages,post_off
    where sec_detail.vlocation=villages.vlocation
    and sec_detail.ac_no=62
    and sec_detail.part_no=69
    and sec_detail.postoff_id=post_off.postoff_id

Share This Page