.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

how to return records in squence of inner join table?

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

View Complete Post

More Related Resource Links

Left Outer Join fails to return all records

I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

joining 3 tables and using join to only include records not found in other table



I have 3 tables. I would like to get the number of records in table A that do not have cooresponding co_asset_ids in TABLE C but also need
to be be found in TABLE B and where the asset_id = 3. Said another way: If TABLE A record is in TABLE B and does not find a co_asset_id of 3 as found in table C then show these records.

assetno 4 and 5 are irrelevant but added to fill out the data more.

I know I could do this getting the co_asset_id column into TABLE A and then do some joins between TABLE A and TABLE C and leave TABLE B out, which I have done in the past. I would like to finally know if this can be done by using the 3 tables together in one elegant query.

The result of this query for this example data  would be:

Table A

Table B
assetno    co_asset_id
1              11
2              22
3              33
4              44
5              55


Return records from 1 table where no matching in 2nd


Hi.  I'm having trouble getting all the results I need with a query and hope its just my lack of experience.

Tables are Employee, Performance_log and Performance.

I need to see all Employees that have a performance_Log entry within a date range.  (done)

Then I was asked to include employees that do not have an entry in the Performance Log table but only if they don't have an entry within the date range previously mentioned.

I feel like I am almost there.  I can display all employees with performance logs and filter by date.   I can display all employees without performance logs within the same query.  My trouble is when an employee has a performance log outside of the date range.  The record should be included but its not.  I've tried using not exists() and not in() in the where but haven't figured it out yet.

Here is the query (the 4th line of the OR is a work in progress and is changing by the minute as I search for the solution)

The dates will eventually be replaced with variables and saved as a stored procedure to be called by Crystal.

  Employee.Employee_Id, Employee.Department, Employee.Active, Employee.NC_Type, Employee.NC_Full_Name,
  Performance_Log.NC_Performance_Note, Perfo

Linq join fields for return data


  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

Writing Webservice return values to table within SQL CLR

Visual Studio 2010/Sql Server 2005/ .net 3.5/ C# I have compiled and created assemblies and all works fine when all I do is .pipe.send.   I have changed it now to capture the values in local parameters so I can write to a sql table on the same server.  I have the connection to the database and the table created.  The code compiles fine but when I run it from sql I get the following error.  The code follows the error.   Msg 6522, Level 16, State 1, Procedure CventSP, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "CventSP": System.Data.SqlClient.SqlException: Incorrect syntax near '@eventtitle'. System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at StoredP

Duplicate records find in table

  Hi ALL, I want to find the dynamically check the primary key values and remove the duplicate records.. please suggest it.   Regards Ram

Selecting different no. of records from the same table

Hello all, This might seem a basic thing but bothering me from last few days. Suppose I have a table containing 5 million records and I am running queries one by one (one query executes completly and then next query is fired)as below on that table. SELECT top 20000 FROM MyTable SELECT top 200000 FROM MyTable SELECT top 2000000 FROM MyTable  Now my questions are; Will SQL Server use same execution plan? As I am running these queries one by one in same order as given above, will second query take less time than it should if run standalone (only second query is fired, no query fired before that) and similarly will third query take less time than it should if run standalone (only third query is fired, no query fired before that), since there is a possibility of same execution plan being used?  Regards.    

I have relation many to many - how can I delete records from intermediate table ?

I have tables: Books id title Authors id name BooksAuthors book_id author_id Entity Framework generated only models for tables Books and Authors in relation many to many. How can I delete from table BooksAuthors all records where book_id =5 ?

Get the count of the recently added records to a table

i need to create 2 temporary tables. The records inserted into the 2nd table is the same as that from table1. Now, i am inserting some other records into table 2.what i need is, to retrieve the count of the recently added records into table2.How to achieve this? 

More than one records return error

Hi Below is my query used in procedure. This query can return no records or more than one records. If this is the case then I wanted to return error message. How to do this? Select Band, SimpleBand into <complexband>, <simpleband> from HHSRS_HazardBand where lowerbound <= <hazardscore> and upperbound > <hazardscore>  

Cannot Insert records in table using NHibernate

I am very much new with NHibernate. I am inserting a new record in the table. However, it seems that it does not insert. What I'm using is Fluent NHibernate.Below is the code which inserts records. public virtual void Save(string CreatedBy) { ISession NHSession = Star.CNPL_BusinessObjects.Common.Session.OpenSession(); ITransaction NHTransaction = NHSession.BeginTransaction(); try { this.Save(CreatedBy, NHSession); NHTransaction.Commit(); NHSession.Flush(); } catch (Exception ex) { NHTransaction.Rollback(); throw ex; } } public virtual void Save(string CreatedBy, ISession Session) { try { if (this.IsPersisted) { this.SaveAuditTrail(Session, false, CreatedBy); Session.SaveOrUpdateCopy(this); } else { Session.Save(this); } } catch (Exception ex) { throw ex; } }

Upload millions of file records to a table

I have six input files having millions of record. I need to upload these data to my table. How can I upload the details in the most efficient way. Any help on this will be greatly appreciated.

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Problem with listing records from table (object not found)

Hi everyone, I'm creating a website based on ASP.NET MVC 2 and SQL Server 2008 R2 Express for the database. After creating the table in database, I want to list all records in that table. For that, I use "scaffold'ing in ASP.NET to automatically generate the view file. But I encountered an error when loading that page. The error message was: [CODE] Server Error in '/' Application. Invalid object name 'dbo.Accommodations'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.Accommodations'. Source Error: Line 41:         </tr> Line 42: Line 43:     <% foreach (var item in Model) { %> Line 44: Line 45:         <tr> Source File: d:\MyProject\Views\Accommodation\Index.aspx    Line: 43 Stack Trace: [SqlException (0x80131904): Invalid object name 'dbo.Accommodations'.]    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2030802    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009584 &n

T-SQL 2005 for same table join?

 I have below table with two columns.. Type      Code AB        Company_chris BC        Company_chis DE        Company_chis AB        Company_bob AB         Company_James BC        Company_James AB         Company_mark DE         Coampny_mark BC        Company_scott Unique value in TYPE column : AB , BC, DE Primary Key is :  TYPE and CODE I’m looking output in result query ......... Code                  Type1     Type2     Type3 Company_chris      AB         BC           DE Company_bob      AB         NULL       NULL Company_mark      AB         NULL       DE Company_scott      NULL      BC        NULL   Any t-sql 2005? Thanks.
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