.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 inner join issue

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
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

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

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

Data query task issue

Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

Issue with CAML Query

I am having a RadGrid in Sharepoint Webpart with each row having a checkbox and multiple Columns where one of the Columns is ColumnID. When a multiple checkboxes are selected, their respective ColumnID Value is used to Query and generate report. I am using CAML Query to pass in the ColumnID of each checkbox selected and generate report based on the ColumnID Value. But I am having issue with the Query I have written. I have tried two ways: One of the ways was to Hardcode just two ColumnID Values as I was not sure how to read array of multiple columnID values. But I was wondering where I was going wrong since it was throwing following error: "Multiple with same name ColumnID were used" Telerik.Reporting.ReportParameter param1 = new Telerik.Reporting.ReportParameter(); param1.Name = "ColumnID"; param1.Type = Telerik.Reporting.ReportParameterType.Integer; param1.AllowBlank = false; param1.AllowNull = true; param1.Text = "ColumnID"; param1.Visible = true; param1.Value = "200"; this.ReportParameters.Add(param1); Telerik.Reporting.ReportParameter param2 = new Telerik.Reporting.ReportParameter(); param2.Name = "ColumnID"; param2.Type = Telerik.Reporting.ReportParameterType.Integer; param2.AllowBlank = false; param2.AllowNull = true; param2.Text = &qu

Outer join operators *= and =* are not supported in 90 or later compatibility modes Upgrade issue

Hi all In processes of upgrading SQL Server 2000 to SQL Server 2008 I held up with one issue I am not a good pragrammer If some helps me how to modify this query,realy it help for me. "Outer join operators *= and =* are not supported in 90 or later compatibility modes" Here this query SELECT Brnd.Name, Dev.Devype FROM Prd,PrdRel, Rel, RelVer, DevSoft, DevCla, Dev, Brnd,OVer WHERE Prd.ID = PrdRel. Product AND Rel.ID = PrdRel.Release AND RelVer.Release = Rel.ID AND DevSofte.ReleaseVersion = RelVer.ID AND DevSoft.DeviceClass = DevCla.ID AND DevCla.ID = Dev.DeviceClass AND Brnd.ID = Dev.Brand AND OVer.ID =* Dev.OSVersion AND RelVer.Latest = 'Y' AND Prd.ID = @Product AND Rel.OS IN (@OS) Please some help me how to code  to come out from the problem. your help going to be greate for me.  SNIVAS

Issue with one of simple query

Hi team, I have a issue in the one of my query select   * from Prod3 where pnumber in (select distinct Policy from prod3_excel) order by pnumber This query gives me 8361 records and there are 8419 records in Prod3 how can i get the remaining records in Prod3 that is 58? select * from Prod3 where pnumber not in (select distinct Policy from prod3_excel) order by pnumber why this is not working? Thanks, Eshwar

SQL query issue

Hi, I have a question that is pricking me as to how to solve it? Here is my requirement and table structures: create table #docs ( docid int, setupid int, docfname varchar(20), docmname varchar(20), doclname varchar(20), ) create table #setups ( docid int, setupid int ) -- A single doc can have multiple setups insert into #docs select 1, 100, 'Spock', 'Moreau', 'Livingston' union select 2, 101, 'Demento', 'Dolittle', 'Kevorkian' union select 3, 103, 'Faust', 'Zhivago', 'Jekyll' union select 4, 104, 'Freud', 'Laura', 'Phil' insert into #setups select 1, 200 union select 1, 300 union select 2, 200 union select 2, 400 union select 4, 200 union select 4, 500 /* If the doc has multiple setups I would like to copy over the data for the all the setupids as below */ select * from #docs select * from #setups drop table #docs drop table #setups I am trying to get this result using union and left out join etc., using a query and not using cursors/loops. Please help me and thanks for the responses. docid setupid docfname docmname doclname 1 100 Spock Moreau Livingston 1 200 Spock Moreau Livingston  --- data copied over 1 300 Spock Moreau Livingston  --- data copied over 2 101 Demento Dolittle Kevorkian 2 200 Demento Dolittle Kevorkian    --- data copied over 2 400 Demento Dolittle&n

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

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

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:

Another Issue with CAML Query



I know this question has to be put in Telerik Forums but its something related to CAML and data being bound programmatically. So, please take a minute to share your thoughts on my issue. Thanks.

In my previous post here , I had issue with CAML Query, which works great now. So, here is one of my issue related to it: I am using a Report which has Subreport. So, I have set the datasource of Main Report to be a DataTable that holds the CAML Query (the result CAML Query from previous post here ). Now, for the same parameter values, for each Report, I want its subsequent subreport to be be populate programmatically. But, this CAML Query do

Issue with CAML Query regarding date comparision.

I have a below query in one the eventhandlers. All I do in the query is to get all records where Next_Date column is < CurrentDateTime ( I have shown current date below in full date format but it will be variable).

What I am observing is the comparision seems to be failing or not considering time format. For example the below query should not return any records when NEXT_DATE is 9/30/2010 23:59:59 and CurrentDateTime is 9/30/2010 06:22:22. However the query does return records. It looks like the comparision is just happening with Date and not time. What could be the problem? Please suggest.

         <FieldRef Name='Next_x0020_Date' />
         <Value Type='DateTime'>2010-09-30T13:13:13Z</Value>
      <FieldRef Name='Next_x0020_Date' Ascending='False' />

Query String Issue


Hello everyone,

I just started with learning MVC so please bear with me. I have a grid displayed on my Main page(Index) and when I click on the Details Link for a particular row(suppose productID=1) it should invoke the following action otherwise redirectToAction("Index"):


        // GET: /Home/Details
        public ActionResult Details(int? ProductID)
            if (!ProductID.HasValue)
                return RedirectToAction("Index");
                return View("Details");

The ProductID in my case is always Null and it actually ends up redirecting back to the main page as expected. Why is that happening. Somebody please help.

Thank You

REST Query Batch Issue


Hi All,

I've got an issue when querying the listdata.svc from a Silverlight client.   

I construct the query as follows:

var parametersQuery = context.Parameters.Expand("AssociatedTask") as DataServiceQuery<ParametersItem>;

If I execute the query individually and then process the results then the AssociatedTask collection gets populated, if I include it as a batch then it does not.

I've examined the query in Fiddler and as far as I can tell the request and response are identical.

Anyone shed any light on the issue?

A query plan issue


There are 3 paths in stored procedure p2:

create proc p2
 @n int
 declare @sql varchar(100)
 if @n=1
 set @sql='select *from ta where id='+cast(@n as varchar(10))
 else if @n=2
 set @sql='select *from tb where id='+cast(@n as varchar(10))
 set @sql='select *from tc where id='+cast(@n as 

Performance issue with select query



I have simple select query as below

SELECT DISTINCT BCEGPrsLinesCd, BCEGCmlLinesCd,CountyFedInfoPrcStdNbr

FROM TPSeleBCEGAddress WITH(nolock)




AND LTRIM(RTRIM(PrefixStreetDirCd))='E'






AND LTRIM(RTRIM(BCEGEffectiveYr))='1998'

Table TPSeleBCEGAddress has 64 Lakhs records in it and it takes around 22 Seconds to execute this query. Please let me know a way to improve performance of this select query.

Primary key defined on this table comprises of 16 columns.






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