.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL query Joins

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
I have 3 different tables.... Pls find the tables and sample data below. I am trying to find count by joining these 3 different tables by tblid and parenttblid. There are tables which fall under different parent tables 'tblrelation' table has this information. The 'tbl' table has tblid and the tbl_code. The 'tblreg' has the tblip and tbl_code. How do I join these 3 table to get the tblid count for different subregion tables. select   * from tblrelation tblid parenttblid 200 101 201 101 202 101 203 101 204 101 205 101 206 101 207 101 select   * from tbl tblid tbl_code 200 tbl_200 201 tbl_201 202 tbl_202 203 tbl_203 204 tbl_204 205 tbl_205 206 tbl_206 207 tbl_207 select   * from tblreg tblip tblreg 791 101 792 101 793 101 795 101 797 101 798 101 799 101 801 101 802 101   SELECT   COUNT(ts.id), tr.tblid FROM   tblreg tz   INNER JOIN tblrelation tr ON tr.parenttblid = td.tblid   INNER JOIN tbl t ON t.tbl = td.tblid   INNER JOIN tabsubs ts ON ts.tblip = tz.tblip   INNER JOIN vtabs vt ON vt.id = ts.id GROUP   BY tr.tblid

View Complete Post

More Related Resource Links

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

Using joins for this query, is it possible

I was wodnering whether its possible to use joins for this weird query, the subquery as i understand isnt the best for performance.   SELECT c.PersonURN, c.CDRValue, sm.CDRValue AS [Customer Value], r.CDRValue AS [Supplier Value] FROM tblProfile c left join tblCustomerCDRLookup sm on sm.PersonURN = c.PersonURN AND sm.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP') left join tblSupplierCDRLookup r on r.PersonURN = c.PersonURN AND r.PriceMatchCode = (select vc.PriceMatchCode from tblPricePlan vc where vc.PlanName='VOIP EXTRA') AND r.AddressURN = c.AddressURN

Very slow performance with a query involving JOINS

I am experiencing extremely slow performance on a particular SQL query using SQL compact.  This same query completes in less than one second on my regular SQL server.  With SQL compact, I am actually not even sure if the query ever completes.  As I type this I am testing it from SQL management studio, and it's up to 17 minutes and 40 seconds still executing so far. Since I am not experiencing this on most of my other queries, I have little doubt the slowness is due to the volume of data in these tables, coupled with the fact that I am doing the joins.  I made sure that foreign key constraints are in place for the columns that the joins are based on.  From what I've read, this also ensures that appropriate indexes are created, although I am starting to wonder about that. This is the query: SELECT TOP (200) t.BusinessEntityID, t.BusinessName, t.IsTowTruck, t.IsHospital, t.TowTruckLastUsed, t.BusinessLocked, cn.Address1, cn.City, cn.State, cn.PostalCode, cnp.PhoneNumber as TopPhoneNo, GetDate() as LastUpdatedDate, GetDate() as CreatedDate FROM tblBusinessEntity t LEFT JOIN cnBusinessEntityLocation cn ON cn.cnID in (SELECT TOP (1) cnID FROM cnBusinessEntityLocation where cnBusinessEntityLocation.Active = 1 AND cnBusinessEntityLocation.BusinessEntityID = t.BusinessEntityID

How do I create a query that joins two tables and returns a single entry with the most recent update


Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

Query on solution which needs help with joins or group by


Wonder if anybody can help me with a solution for this query.

Have been asked to flag entries on a table as deleted where there are matching policy premiums. I have created 2 temp tables called #AMPneg (which contains RowID, Premium Value, Absolute value of premium and policy id for all premiums < 0) and #AMPpos which is the same apart from being for premiums > 0.

I have tried running the following sql:

SELECT      AccountsModulePaymentKeyNeg,

        PaymentAmountOriginalCurrencyNeg ,

        AbsolutePaymentNeg ,

[Sql Server 2005] Need help to query with joins


Hi all :)

I hope anyone save me from madness ...

here is my query

                      t1.date, t1.employeeID, t2.name AS customer,

                     t3.description AS service, t4.total as amount

FROM            Trg_EmployeeCalendar AS t1 LEFT OUTER JOIN
                    EmployeeJobs AS t4 ON t1.employeeID = t4.employeeID AND t1.date = t4.date LEFT OUTER JOIN
                    Customers AS t2 ON t2.id_location = t4.locationID LEFT OUTER JOIN
                    ServiceCategory AS t3 ON t3.id_service = t4.serviceID LEFT OUTER JOIN

With this query, if an employee has two jobs in different l

How to remove duplicates from query results caused by table joins


It's been a while since I used SQL and I'm coming across a problem where by the database design is adding a lot of useless output to a query I need to run and I'm not very clever so don't know how to remove the crap I don't need.

This is what I'm up against:

Create Table #Planning(Assy nvarchar(255), PartNo nvarchar(255))
Create Table #PlanningCosts(Assy nvarchar(255), MaterialCost float, LabourHours float, LabourCost float)
Create Table #PlanningLabour(Assy nvarchar(255), OpNo Nvarchar(255), OpDesc nvarchar(255), OpTime float)
Create Table #PlanningMaterial(Assy nvarchar(255), Material nvarchar(255), MaterialQty float)

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Query an arraylist of array list using linq


I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account

Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100

I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend