.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 nested use of count and join

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

Here is the problem/issue:  Table A can have multiple record associations to Table B.  Table B has a 1:1 record relationship with Table C.  Table C can have multiple record associations to Table D.  I want to find all the records in Table A that have exactly one entry in Table B, which have only one entry in Table C (which is always true as noted above), which have 0 or 1 entry in Table D.

Here is what I have now:

use Database
Select *
From TableA as TA
Where TA.MedRecID In
    select TableB.MedRecID
    from TableB,TableC
    where TableB.AccountSourceIdentifier In
        select TableC.IncidentSourceIdentifier
        from TableC, TableD
        where TableC.IncidentID In
            select TC.IncidentID
            from TableC as TC
                left outer join TableD as TD

View Complete Post

More Related Resource Links

How to join two sharepoint list in c# by CAML query?

Hii All,
   I am using wss 3.0 and i have one site and site has 4 to 5 lists. lists are interconnected with lookup column and same column. I need to display some column fron one list and some from another.. i need to join that lists by where condition like project =project..

So how can i join??

Thank You

Count Query Execution


I have a class that being used to connect with the DB. Now I want to count how many times each web request executes the queries, but I've no idea where to store the counted value. I mean, Session wont, ViewState wont work as site also have webservices. What else I can use ?


We have the following query:   SELECT H.VEHICLE_ID,                 COUNT(ISNULL(H.HISTORY_ID, 0))            FROM VEHICLES V LEFT OUTER JOIN VEHICLE_HISTORY H              ON V.VEHICLE_ID = H.VEHICLE_ID           WHERE H.SPEED >= 65             AND CONVERT(DATE, H.GPS_TIME) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))        GROUP BY H.VEHICLE_ID;   Unfortunately, if there inst a record for a vehicle for the current day in VEHICLE_HISTORY, or there inst a record over 65 in VEHICLE_HISTORY, the query simply does not include the result. How can we include all results (i.e. return 0)? What we want is the number of rows returned to match the number of rows in the vehicles table. I thought doing the LEFT OUTER JOIN would take care of this, and return a result for all vehicles.   Thanks.

Cross list query for two sharepoint lists and a join between the fields

Hello All, I have a requirement where i need to query the data from two  sharepoint lists  on a same site by doing a join between those lists and get the result into datatable.I am doing this using sharepoint object model.Is there any possibility to do this. Any pointers in the right direction are greatly appreciated. Thank you Smith   smith

Sql query inner join issue

so i have a datagridview in a vb from. my plan is to display a table named emplyees, having the following fields: [employees] ([lastname], [firstname], [cost_center], [division], [job_title], [hiring_date], [exit_date], [functional_manager], [member_of], [backup_by], [out_of_office], [desk_phone], [mobile_phone], [comments]. But I need to make an inner join with 3 other tables. job_titles, divisons and rss. How should i writethe select command for this, having the inner join forthe follwing fields: division- division.name,job_title-job_titles.name , and member_of- rss.group_name. I have no idea how to do it. pls help Thanks

tfs mdx query(cumulative count)

I need cumulative count of bug,risk work item per day first, i made this query WITH MEMBER [Measures].[Date Value] AS     [Date].[Date].CURRENTMEMBER.MEMBER_VALUE   MEMBER [Measures].[Active Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Active], [Measures].[Cumulative Count])   MEMBER [Measures].[Resolved Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Resolved], [Measures].[Cumulative Count])   MEMBER [Measures].[Closed Cumulative Count] AS ([Date].[Date].CURRENTMEMBER, [Work Item].[System_State].[System_State].[Closed], [Measures].[Cumulative Count])   SELECT {     [Measures].[Date Value],     [Measures].[Active Cumulative Count],     [Measures].[Resolved Cumulative Count],     [Measures].[Closed Cumulative Count] } ON COLUMNS, (     Filter(         [Date].[Date].[Date],         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE >= CDate(@StartDateParam) AND         [Date].[Date].CURRENTMEMBER.MEMBER_VALUE <= CDate(@EndDateParam)     ) ) ON ROWS FROM [Team System] WHERE (     {[Work Item].[System_WorkItemType].&[Bug],[Work Item].[System_WorkIt

Problem with SELECT COUNT query and parameters

Hello!I have a problem with SELECT COUNT query in ASP.net. I want to create CMS with articles which have categories (which have the option to be deleted). The problem is that I want to get the number of articles within the specified category so if there aren't any articles with the specified category I can proceed with the category deletion.I have the following code:protected void Page_Load(object sender, EventArgs e) { } protected void GridViewKategorije_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "Uredi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell ClanakID = odabraniRed.Cells[2]; string ID = ClanakID.Text; Response.Redirect("/Portal/Administracija/Kategorija.aspx?idKategorija=" + ID); } else if (e.CommandName == "Obrisi") { int index = Convert.ToInt32(e.CommandArgument); GridViewRow odabraniRed = GridViewKategorije.Rows[index]; TableCell KategorijaID = odabraniRed.Cells[2]; String connString = WebConfigurationManager.ConnectionStrings["CMS"].ToString(); SqlConnection conn = new SqlConnection(connString); conn.Open(); using (SqlC

MDX query to count non empty descendants


I have a calculated measure with the following definition,

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] /3

Instead of hard-coding the division value, I want to count how many non-empty descendants [Admit Date].[Date Hierarchy].[Quarter] has and divide the measure by that number. I wrote some Query like this, but didn't give me any result.

When [Admit Date].[Date Hierarchy].currentmember.level is
        [Admit Date].[Date Hierarchy].[Quarter]
Then [Measures].[Members] / COUNT(NONEMPTY(DESCENDANTS([Admit Date].[Date Hierarchy].[Quarter])))

How should I correct this query?




query join problem

I am using CTE and joining it to other tables in my query.  The issue is I am not creating my joins correctly.  I am using the CTE twice but the calues in it should only appear one time on amount based on the amount type.  attached is the code
declare @dt datetime
set @dt = getdate();

With itmlvl as (





select distinct top 10000 dnc.EM_ITEM_NUM
			,dnc.amt as DNC 
			,pur.Amt as PUR_AMT
			,psr.AMT as PSR_AMT
			,pur.RBT_TYP_ID purRBT_TYP_ID
			,pur.DSCR purDSCR
			,psr.RBT_TYP_ID psrRBT_TYP_ID
			,psr.DSCR psrDSCR
 from geprs_dnc..T_Item_cost_approved dnc
		join reference..T_iw_em_item ref 
		on ref.em_item_num = dnc.em_item_num

		INNER JOIN itmlvl 
		ON itmlvl.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		INNER JOIN itmlvl pur
		ON pur.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and pur.RBT_TYP_ID = 101
		INNER JOIN itmlvl psr
		ON psr.em_item_num = dnc.em_item_num	
		and @dt between dnc.EFF_DT and dnc.END_DT 
		and psr.RBT_TYP_ID = 102

where dnc.COST_ID =32

So for PUR and PSR there should only be one amount for the given date range ...i've

how to count transactions done on your database by T-SQL query?


Dear All,

I have multiple database and I want to know how many transaction that are happening per database using T-SQL, can anyone help me?





Karim Alaa

Performance tuning > two tables with strings > best query to join them


I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.

Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.

update query qith inner join


i have 3 tables ,and i have inserted data in table A from  tables B and C into it

"my A table has column"
Coe_Code , Coe_name , Sdo_code, Sdo_name

"B table has column"

COE_ID,  Coe_Code , Coe_name  (COE_ID is auto incremented)

C table has column

Sdo_id ,Sdo_code , Sdo_name, COE_ID  (Sdo_id is auto incremented and COE_ID coming from B table )

i have inserted data into B table from A
after that i have inserted data into C by taking column from A and B as you can see column and table above by using this query

[insert into C (Sdo_code,Sdo_name,COE_ID ) 
select A.Sdo_code,A.Sdo_name,B.COE_ID from A,B where A.Coe_Code =B.Coe_Code

but my problem arise when i need to update my C table ,i need to update only 2 column (Sdo_name,COE_ID ) in C table coming from 2 different table
i tried so many query using inner join with update  but was not able to update that..

if some one could help me to provide the query..i also tried this query but didnt work...
[update C set Sdo_code=A.Sdo_code,COE_ID=B.COE_ID from C innerjoin A on A.Sdo_code=C.Sdo_code innerjoin B on C.COE_ID=B.COE_ID]

please provide me the query or help me in what ever way you can

Nested Full Text Query and SQL syntax



Quick question, is it possible to use the SQL TOP syntax in a Full Search Query something like

Select Top 1 title from SCOPE() where = 'All Sites' AND Productid= '1234556'


Can you use nesteed select statments ?

Select title from SCOPE() where = 'All Sites' AND Productid=  (select productid from scope() where scope = 'all sites' AND product = 'example')


Dave Roberts


Help :Is this Query right? How to Get the Result: Which to use union OR join AND How?

Can you please help me?


SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11


SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:

nested query in sql


ALTER PROCEDURE dspupdbyport
@portcod int
select *,(select prfnam from tbprf where prfcod in(select updprfcod from tbupd where updportcod=@portcod))nam from tbupd where updportcod=@portcod

i want to retrieve prfnam of sender (scrapsndrcod) from tbprf and the scrapmsg send by them from tbscarp but it gives n error msg that nested query returns multiple values and is not allowed in =,<== etc operators

what to do man its urgent please help   

SSRS MDX Cross Join Query - Cross join Non Empty Set with Empty Set



I'm pretty new to MDX so please go gentle :)

I'm writing a SSRS report which will show employees (in a hierachy) and the number of different events which occured for them each day over a week.

The problem i'm having is that i want to show ALL employees in the hierachy regardless of whether they have any events but also ONLY show those event columns for events which have happened that day.

As i understand it so far SSRS does cross joins to flatten out the data, so i'm guessing i need to crossjoin the Employees set (including empties) against the events set ensuring NON Empty is used, however i'm struggling to get this to work

Heres what i've trued so far

NON EMPTY{[Measures].[Event Count] } ON COLUMNS, 
{DESCENDANTS([Employee].[Reports To].[Regional Director].ALLMEMBERS)} ON ROWS 
FROM [DataWarehouse]


Event                  Count
Employee1            28
Employee2            28
Employee3            (null)
Employee4            28

Nested transactions, rollback causes error "Transaction count after EXECUTE indicates that a COMMIT



I have a stored proc that does something that requires a transaction, and so I've put in the begin tran, commit, and rollback on error logic in it. That works as expected. But what if I now want to use the proc in the context of a larger transaction, such as calling it multiple times or doing other things as part of the same transaction? This seems impossible to achieve without either raising an error (throwing an exception) or having SQL Server do so on my behalf, rather unhelpfully.

For instance, I wanted to test a proc and reckoned a simple way to do so would be to start a transaction, execute the procedure, and roll back the transaction if the procedure didn't abort it:

begin transaction 
exec someProc
if XACT_STATE() = 1 rollback

This works if someProc doesn't encounter any errors (so it begins and commits a transaction and the transaction count is 1 at entry and exit of the proc), but crashes and burns if the procedure rolls back the transaction, producing SQL error

Msg 266, Level 16, State 2, Procedure ExecuteImport, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

I don't understand why that is. To my mind, it is perfectly normal that the transaction count can go from any level at entry to ze

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