Convert the SubQuery into Join Query

Posted: Jan/22/2011 By: nikhil Points:15 Category: .NET - ASP.Net Views:117 Vote Up (0) Vote Down (0)

Hi,

I had written the Below SubQuery and my result is comming correctly

SELECT dimTerritory.territoryId ,dimTerritory.territoryName from dimTerritory
INNER JOIN dimRegion ON dimTerritory.regionId=dimRegion.regionId
WHERE dimRegion.regionId IN
(
    SELECT dimRegion .regionId from dimRegion INNER JOIn
    dimZone ON dimZone.zoneId=dimRegion.zoneId WHERE dimZone.zoneId IN
    (
        SELECT zoneId from dimEdgeZRTMapping WHERE secgroupId=250
    )
)

OutPut:

territoryId TerritoryName

4    AGRA
10    ALIGARH
30    BAREILLY
34    BARWANI
50    BHOPAL
63    CHINDWARA
73    DHAR
95    GWALIOR
97    HARDA
104    HOSHANGABAD
108    INDORE
115    JABALPUR
132    KANNAUJ
138    KHANDWA
140    KHARGOAN
145    KOTA
167    MANDSAUR
172    MEERUT
175    MORADABAD
177    MUZAFFARNAGAR

When i Try to Convert the subquery into Join Query data is coming wrong .Where is wrong in my Join query

SELECT dimZone.zoneId,dimZone.zoneName , dimRegion .regionId,dimRegion .regionName ,dimTerritory.territoryId ,dimTerritory.territoryName
from dimTerritory
INNER JOIN dimRegion ON dimTerritory.regionId=dimRegion.regionId
INNER JOIn dimZone ON dimRegion.regionId=dimZone.zoneId
INNER JOIn dimEdgeZRTMapping ON dimZone.zoneId=dimEdgeZRTMapping.ZoneId
WHERE dimEdgeZRTMapping.secgroupId=250

OUPUT:-

1    CENTRAL    1    AHMADABAD    5    AHMADABAD
1    CENTRAL    1    AHMADABAD    33    BARODA
1    CENTRAL    1    AHMADABAD    45    BHARUCH
1    CENTRAL    1    AHMADABAD    102    HIMMATNAGAR
1    CENTRAL    1    AHMADABAD    161    MAHESANA
1    CENTRAL    1    AHMADABAD    237    SURAT

Can any body give the solution into Join query

PS.Shakeer Hussain

View Complete Post

Comments:

Be the first to comment this post.

Post Comment

Please login to post your comment

More Related Resources

Calling javascript funtion: Trying to convert a simple example from html to asp.net

I have this javascript function in a javascript file called Bing.js

function onGeocodeClick() {
    myMap.Clear();
    address = document.getElementById("txtWhere").value;
    StartGeocoding(address);
}

function StartGeocoding(address) {
    myMap.Find(null,    // what
              address, // where
              null,    // VEFindType (always VEFindType.Businesses)
              null,    // VEShapeLayer (base by default)
              null,    // start index for results (0 by default)
              null,    // max number of results (default is 10)
              null,    // show results? (default is true)
              null,    // create pushpin for what results? (ignored since what is null)
              null,    // use default disambiguation? (default is true)
              null,    // set best map view? (default is true)
              GeocodeCallback);  // call back function
}

I have this line in my masterpage referencing my javascript file called Bing.js

<head runat="server">
    <title>Geocoding in Bing Maps</title>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      <link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />
      <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3"></script>

     <script src="Scripts/bingMap.js" type="text/javascript"></script>    <---***bingMap.js        
    <asp:ContentPlaceHolder ID="HeadContent" runat="server">
    </asp:ContentPlaceHolder>
</head>

I have this in my Default.aspx. I have my new asp.net textbox and asp.net button above the htm text and click from the bing example

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <div>
        Address:
        <asp:TextBox ID="txtHere" Width="255px" runat="server" 
            ontextchanged="txtHere_TextChanged"></asp:TextBox>
         
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    <div style="font-family:Arial; font-size:10pt">
          Address: 
          <input id="txtWhere" type="text" style="width:255px" />
           
          <input type="button" value="Geocode" onclick="onGeocodeClick()" />
       </div>
       <br />
       <div style="position:relative;width:400px;height:400px;" id="mapDiv">
       </div>
       <div id="resultsDiv" style="font-family:Arial;font-size:8pt;position:relative; top:-400px;left:425px;width:400px">
       </div>
</asp:Content>

Then this is my code behind for default.aspx.cs

      protected void Button1_Click(object sender, EventArgs e)
    {
        //myMap.Clear();
        //address = document.getElementById("txtWhere").value;
        //StartGeocoding(address);
        string address = txtHere.Text;
        // Get a ClientScriptManager reference from the Page class.
        ClientScriptManager cs = Page.ClientScript;

        // Check to see if the client script is already registered.
        if (!cs.IsClientScriptBlockRegistered(this.GetType(), "StartGeocoding"))
        {
            cs.RegisterClientScriptBlock(this.GetType(), "StartGeocoding", "StartGeocoding(address)");
        }
    }


On Button1_Click(), instead of calling StartGeocoding(address)...it prints the words StartGeocoding(address) at the top of the screen!

Is the ClientScriptBock above suppose to go into Page_Load? How do you make the javaScript call to StartGeocoding(address)?

Thanks, John Feeney

Convert MM/DD/YYYY to YYYY/MM/DD

hello,

When the user enter the data in the text field, they are entering as 12/1/2010 4:19:49 PM.

But in database, the date is in this format 2010-12-01 16:19:49.367.

Here I have to change date format and Convert 12 hr format to 24 hr format.

Can any one help me.

TIA

Webmatrix.Data.Database.Query() method return type

I'm using the Webmatrix.Data.Database.Query method to return a result set in the following form

var myResult = db.Query("Select * from mytable");

How do you cast myResult to get the count of records returned? I'd like to do something like

myResult.Count

Query String Problem

Hi,

I am trying to grab data from a query string but I am having problems as the id within the query string contains a '#' symbol so for example

payment.aspx?id=#12

Now the problem is when I use string id=Request["id"] it doesn't grab the id but does if I remove the hash, any ideas anybody?

Thanks

Steven

SQL QUERY PARAMETER INPUT

Using a text box to accept user input I would like accept the value from the textbox for @DESCRIPTION

I'm getting stuck on that part. How do I get the input from the text box into my SELECT parameter so it will search on this value ?

Code example below;

THANKS FOR YOU HELP !!

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        
        Dim SQLConnStr As String = ConfigurationManager.ConnectionStrings("IMDEquipDBConnectionString").ConnectionString


        Dim selectSQL As String = "SELECT * FROM tbl3161IMD WHERE DESCRIPTION LIKE @DESCRIPTION"


        Dim mySQLconn As New SqlConnection(SQLConnStr)

        mySQLconn.Open()

        Dim SQLCommand As New SqlCommand(selectSQL, mySQLconn)

        

        Dim adapter As New SqlDataAdapter(SQLCommand)

        Dim ds As New DataSet

        adapter.Fill(ds, "tbl3161IMD")



        GridView1.DataSource = ds
        GridView1.DataBind()

        mySQLconn.Close()




    End Sub
End Class

passing parameters for a SELECT query to another page

I have an asp.net site which includes a "search" page that queries an SQL table, then redirects the user to a "results" page where the results of the query are displayed.

The parameters for the query are assembled into a string which is the SELECT statement.

I've been asked to add additional functionality that requires a secondary search which takes place on the "results" page. How can I pass the parameters from the "search" page to the "results" page so that I can access those parameters in the code behind?

Thanks for any help.

Here's the first page code behind (I inherited most of this from another developer):

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Maine_Innkeeper_Site_2010
{
    public partial class searchtest : System.Web.UI.Page
    {
        private string name = string.Empty;
        private string city = string.Empty;
        private string desc = string.Empty;
        private string propType = string.Empty;
        private string regionStr = string.Empty;
        private List<string> amenities = new List<string>();
        SqlCommand cmd = new SqlCommand();

        //if the form is submitted...
        protected void submit_Click(object sender, EventArgs e)
        {
            if (gatherDataFromForm())
            {
                string sql = buildSQLStatement();
                Session["lodgingSearchSql"] = sql;
                Response.Redirect("lodging-search-output.aspx");
            }
        }

        //if the image map is clicked...
        protected void ImageMapClicked(object sender, ImageMapEventArgs e)
        {
            //establish a variable for the value of the region clicked  
            string Region = e.PostBackValue; 

            //build the query
            bool needAnd = false;
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID, PropertyName, City, Image1, Image2, InnLinkURL, EnhancedListing ");
            sb.Append("FROM members WHERE Region = '" + Region +"' ORDER BY City, PropertyName");
            string sql = sb.ToString();
            Session["lodgingSearchSql"] = sql;
            Response.Redirect("lodging-search-output.aspx"); 
        }

        private bool gatherDataFromForm()
        {
            name = common.scanForSqlAttack(PropertyName.Text);
            city = common.scanForSqlAttack(PropertyCity.Text);
            desc = common.scanForSqlAttack(PropertyDescription.Text);

            try
            {
                propType =
                   common.scanForSqlAttack(PropertyType.SelectedValue).ToUpper();
            }
            catch
            {
                propType = "";
            }
            try
            {
                regionStr = common.scanForSqlAttack(region.SelectedValue);
            }
            catch
            {
                regionStr = "";
            }
            for (int i = 0; i < amenityList.Items.Count; i++)
            {
                if (amenityList.Items[i].Selected)
                    amenities.Add(amenityList.Items[i].Value);
            }
            return true;
        }

        private string buildSQLStatement()
        {
            bool needAnd = false;
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID, PropertyName, City, Image1, Image2, InnLinkURL, EnhancedListing ");
            sb.Append("FROM members WHERE ");
            if (name.Trim() != "")
            {
                //if (ExactName.Checked)
                 //   sb.Append("PropertyName = '" + name.ToUpper() + "'");
                //else 
                sb.Append("PropertyName like '%" + name.ToUpper() + "%'");
                needAnd = true;
            }
            else  // not selected by name, use the other selectors
            {
                if (city.Trim() != "")
                {
                    if (needAnd)
                        sb.Append(" AND ");
                    else needAnd = true;
                    if (ExactCity.Checked)
                        sb.Append("city = '" + city.ToUpper() + "'");
                    else sb.Append("city LIKE '%" + city.ToUpper() + "%'");
                }
                if (regionStr.Trim() != "")
                {
                    if (needAnd)
                        sb.Append(" AND ");
                    else needAnd = true;

SQL Query

Hi,

I'm stuck on figuring out how to write this query - it's probably some something quite basic but I've been looking at it for too long.

I want to search a table for two parameters from the same column where one of the parameters equals a value. How do I go about this?

Sorry for such a simple question!!

Thanks

Kristin

How to read excel file and write into table through sql query

how to bind in query in sqldatasource based on listbox multiple selected item

Hi..

I need to bind sql IN query in sqldatasource the IN query will bind on dropdownlist and dropdownlist will show databased on Listbox multiple selection how to do this

I need to method

1) normal sql query

2) another one is storeprocedure

Inserting a condition value in xml query

Hello, I was hoping someone could show me how to fill in the "condition" value correctly. If I run the xml script the Condition value must be like: <Conditions> Email = "jackson@shaw.ca"</Conditions>. So how to I get my linq query to show this. Please note that the email address must be called from txtContactEmail. Thank you

                var xmlRequestFind = new XElement("FindPartnerContactsAction", new XAttribute(XNamespace.Xmlns + "xsi", "http://www.w3.org/2001/XMLSchema-instance"), new XAttribute(XNamespace.Xmlns + "xsd", "http://www.w3.org/2001/XMLSchema"),
                                           new XElement("CompanyName", CompanyName),
                                           new XElement("IntegrationLoginId", login),
                                           new XElement("IntegrationPassword", password),
                                           new XElement("Conditions",  txtContactEmail.Text)
                                          ).ToString();

XML Response

<FindPartnerContactsAction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <MemberID>admin</MemberID>
  <CompanyName>brick</CompanyName>
  <IntegrationLoginId>me</IntegrationLoginId>
  <IntegrationPassword>password</IntegrationPassword>
  <Conditions>Email = "jackson@shaw.ca"</Conditions>