.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


Posted By:      Posted Date: September 10, 2010    Points: 0   Category :ASP.Net
TABLE 1SELECT I.STUNA    ,S.SNO         ,SUM(B.AMT * Y.YEAR) AS AMT  FROM STUDENT S  LEFT JOIN INFO I ON S.SNO = I.SNO  LEFT JOIN BONUS B ON S.SNO = B.SSNO  LEFT JOIN YEAR  Y ON S.SNO = Y.SSNO  STUNA       SNO      AMTJOHN         A        10LISA         B        20ALLEN        C       100TABLE 2SNO   AMTA     1B     2C     3D     5I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)STUNA       SNO      AMTJOHN         A        11LISA         B        22ALLEN        C       103EDDIE        D         5I TRIED USE THIS QEURY SELECT I.STUNA    ,S.SNO         ,SUM(B

View Complete Post

More Related Resource Links

Join two tables on multiple columns


SQL Server 2008 R1

I have been successfully joining 2 tables on multiple fields with TSQL like:

SELECT a.* FROM a JOIN b ON b.field1 = a.field2 AND b.field3 = a.field4

My questions is:

1. Is this the best way to do this?

2. Are there limitations on how many fields you can JOIN ON or the size of these fields?

Now the specifics.

This application is inserting property conveyances (sales) from a source file (CombList), possibly with field corrections (CombListChanges). There are over 500,000 records in the source file each with a matching correction record. The UPDATE (shown below) updates the SourceID of the inserted records (approximately 350,000). The Conveyance INSERT (not shown) and the Conveyance UPDATE (shown below) each took 3-5 minutes to run.

The second query is an INSERT matching the Conveyances with a Real Property record. As written it runs for more than 2 hours and expands the tempdb to 10s of Gigabytes in size. I have never had it successfully execute. However if I run just the SELECT portion and eliminate the deedamount (money) and deedtype (nvarchar(10)) JOINs it runs in about 30 seconds (and gives me slightly too many records). The SourcePage (nvarchar(100)) is made up of a concatenation of several fields and text.

3. Any idea on why this INSERT won't run in a reasonable time?

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.

Join Two Tables and Prepare Report



            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?


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


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n

any alternative for join tables?

 Hi guys, is  there any alternative for joining tables in ado.net? The problem is i have 2 tables: Transaction pkTransactionID TransactionDate FKTransactionCategory : refers to PKCAtegoryID   Category PKCAtegoryID CategoryName   I need to show a joined information from 2 tables in a gridview and be able to update data in tables. I can join them using linq to entity and show them in gridview. But i need to handle update and insert by myself then. Is there any way to make mapping so i can handle the data in those 2 tables as one table and bind it to bindingsource? Thanks in advance.      

Two Columns of Floating Tables Only Allowing Next Two Tables to start together.

Hi,I am styling a form I have created. I have created a number of tables and I am trying to position them with CSS. However, for some reason each to the two tables are positioned together. So lets say the 1st table on the left is shorter than the table on the right. Then on the second row of tables they both start at the end of the table on the right. I just don't know where I am going wrong. There is no clear command or anything. Any help would be really appreciated. Thanks in advance,      Seamus

Join two tables

I have following SQL command   SELECT     software_communication_comments.observation_comment,child_communication_observation_outcomes.communication_outcome_status, software_community_comments.observation_comment ,child_community_observation_outcomes.community_outcome_status From     child_observation,Child_Basic_Information  ,group_table ,software_communication_comments ,child_communication_observation_outcomes ,software_community_comments,child_community_observation_outcomes  where (child_observation.observation_ID = '2'  and   child_observation.child_ID = Child_Basic_Information.child_ID and group_table.group_ID = Child_Basic_Information.Group_ID) AND (child_communication_observation_outcomes.child_observation_ID = '2' AND software_communication_comments.comment_ID =  child_communication_observation_outcomes.communication_obser_outcomes_ID) AND ( child_community_observation_outcomes.child_observation_ID = '2' AND software_community_comments.comment_ID =  child_community_observation_outcomes.community_obser_outcomes_ID   ) This is working well if both table (child_community_observation_outcomes and child_communication_observation_outcomes) have records belongs to child_observation_ID =2 ,if one table dont have a relevent record it doesn't display anything. I need to ret

Cross Join and error Two sets specified in the function have different dimensionality.

WITH   SET   [InternetSET] AS {   CROSSJOIN ([APM Channel].[APM Channel Desc].&[Interactive] , {        [APM SubChannel].[APM Sub Channel Desc].&[Affiliates]       ,[APM SubChannel].[APM Sub Channel Desc].&[Partner]       ,[APM SubChannel].[APM Sub Channel Desc].&[Unreferred]       ,[APM SubChannel].[APM Sub Channel Desc].&[SMS]       ,[APM SubChannel].[APM Sub Channel Desc].&[Search]       ,[APM SubChannel].[APM Sub Channel Desc].&[Media]       ,[APM SubChannel].[APM Sub Channel Desc].&[E-mails] } ) }   SET   [Top1SET] AS {   TOPCOUNT([InternetSET],1,[Measures].[% Approve]) } MEMBER   [Top1]   AS   SUM([Top1SET],[Approved Apps]) / SUM([Top1SET],[Basic Apps]), Format_String='0.00%'   MEMBER   [Internet]   AS SUM([InternetSET],[Approved Apps]) / SUM([InternetSET],[Basic Apps]), Format_String='0.00%' select { // if I use the set below instead of the member below it I get // error : Two sets specified in the function have different dimensionality [Top1set] // if I use the member below instead of the set above the query works // but the name of the item is not dis

LINQ-to-SQL: How do you join more than 2 tables?

Hi there, I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:  SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17'; can be expressed as: var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 }; But how would I join 3 or more tables using LINQ? For example: SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37'; I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement. Any help will be greatly appreciated! Thanks,- K.  

Discovering ODBC Tables, Columns and column Definitions

I have worked with ODBC data connections for a while.  Usually, I've setup a connection to some type of database (IBM ISeries, SQL Server, Progress, etc.) depending on the project and then I use MS Access to inspect or export the data using linked tables. I have a database that has numerous tables and columns.  In fact, I believe there are some 16k data columns in the entire database.  Furthermore, I have an excel file that gives me a list of all of these tables and a brief description of each column  but it does not have useful data types in the description.  What I would like to do is query the ODBC source and extract each table, it's columns and it's column data types. My end goal is to take the data from the spread sheet (column names and description) and insert that data into a database that also contains the correct, SQL data type in an additional field.  Is there a way to query an ODBC data source to acquire all of this information?

Search a word in all the tables(all columns) of a database

Dear Friends, I am developing the Home page of a Client. Apart from various things on this home page I have a text box and search button.Having said that, I have a database in which I have almost 12 tables with varying number of columns.Now my question is,Is there anyway to search a word typed by a user in the textbox to search it in all the tables(all columns) of the database.A simple,concise and easy to follow solution would be a great help.

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Join two fact tables




I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.



AccountNumber                       char

problem seems to be related to grouping by on columns with the same name in different tables


using OLEDB provider IBMDADB2.1

and the following SQL run from ReportBuilder


sect.name as Team,

tasktype.name as TaskType,










The SQL runs fine in a DB2 query tool but when run through ReportBuilder I get

[DB2/LINUXX8664] SQL0119N  An expression starting with "TASKTYPE" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.  SQLSTATE=42803


Basically the problem seems to be related to grouping by on columns with the same name in different tables.

Anyone know a work around?

Join Tables For Definitions (Data Table/Definition Table)



I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

Drop extended property "MS_Description" of ALL tables and ALL columns


Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?


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