.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

Most Recent records from one of the table among the tables in multiple joins.

Posted By:      Posted Date: September 19, 2010    Points: 0   Category :Sql Server

Hi All

I have a single select query with multiple table joins in it.

I want to retrieve the Most Recent records from one of the table among the tables in multiple joins.


(select t1.col1, t2.col2 , t3.col3, t4.col4, t4.Add_Date,t5.col5,

from dbo.table1 t1

left outer join dbo.table2 t2 on t1.col1=t2.col2

left outer join

(select col3 from dbo.table3 where condition1="expr") as t3

on t1.col1=t3.col3

left outer join dbo.table4 t4 on t1.col1=t4.col4 order by t4.Add_Date

left outer join dbo.table5 t5 on t1.col1=t5.col5 )

Problem is that, I am not able to use the "Order by" for a particular table.



View Complete Post

More Related Resource Links

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

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

how to delete records from multiple tables.


Hi Friends,Tell me How to delete records from multiple tables by using a single query.

deleting records from multiple tables at once in SQL Sever 2005


DELETE Users, meet, tour FROM Users, meet, tour WHERE Users.uid=meet.desg AND meet.desg=tour.desg AND Users.uid='CCC';

i have try this command to to delete record from three table

i want to delete all the record of uid from user ,tour and  meet at once.

Users table having a key uid which is common to all table.

 in table Users, uid is primery key

and meet table having desg and tour having desg common field value

i.e. Users.uid=meet.desg=tour.desg are same desg in both table is foreign key

so how could i delete uid from Users table ,desg from tour and meet at once and all the record from these 3 tables

Saving records in to multiple tables!


hi friends,

im not that familar with xml but im stumple across the problem that i cannot save 1: M relationships when i have to save more than column in the a child table. else could use csv and i could save off one column. But when i have save more than one column in ths second, third...etc i cannot think of anything else than xml.. but im familar with xmal and xpath, xquery all that.

So is there a popular atechnique that the developers use to save records into multple table than use of xml?



Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

HOW to write the one insertion Procure for multiple table Records in sql server 2008


i m facing the Proble

i want to insert values into multiple table in one insertion Procedure is it Possible?

see my info

Table name UserPersonal columns

            ([UserRegform_pkID] PK


second table userRegForm columns

            ([UserRegform_pkID] PK

Nested table 'link' which inherits its namespace cannot have multiple parent tables in different n


Does anyone know what I need to do to get past this error?

The error is "Nested table 'link' which inherits its namespace cannot have multiple parent tables in different namespaces."

I get the error on this line:


 Protected Sub lstOpens_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ConnectStr As String = _

        Dim ctu As String = GetCTU()
        Dim ctp As String = GetCTP()
        'check if user has ct's
        If String.IsNullOrEmpty(ctu) Then
            Exit Sub
        End If
        If String.IsNullOrEmpty(ctp) Then
            Exit Sub
        End If

        'this returns an xml info for selected Campaign (email)

        Dim sUri As String = "https://api.constantcontact.com" & Me.lstOpens.SelectedValue.ToString()
        Dim sAPIKey As String = "/////"

        'setup httpWebReqeust to send the request
        Dim address As New Uri(sUri)
        Dim theRequest As HttpWebRequest = TryCast(WebRequest.Create(address), HttpWebRequest)
        theRequest.Credentials = New NetworkCredential((sAPIKey & "%" & ctu), ctp)


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


Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

search multiple words into textbox and populate all records in gridview related those keywords


I have a textbox and a gridview which is bound to a table

what I want to achieve is a solution where lets say there are two records - e.g. "1. Book and 2. Red", i want a user to type in those words and the gridview shows the results as followed;

1. Book

2. Red

I have set up the sql data source and everything and my like operator is this...

LIKE '%' + @record + '%'

how can I solve this problem?

InfoPath and Access - two parent tables and one child table


Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick

insert new records in tables from dataset to database


I have 2 tables in SqlServer 2008.

Table1: T1id, SomeData

Table2: T2id, T1id, SomeData

I am using DataAdapter.FillSchema to create shema of tables in DataSet. I created DataRelation on columns T1id in both tables.

Now when i try to Update Sql Database T1id in Table2 remains 0 and not the value of T1id in Table1.

I can successfully update Sql Database if i fill DataSet with records first (Using DataAdapter.Fill), but that's not what i want to do. I don't need "old" records in my dataset. I want to use dataset just to store all imputs from user until the proccess is done and then insert all those records "at the same time".

I think i'm getting it wrong with ForeignKeyConstraints, maybe UpdateRule is not set to cascade, i just don't know.

I have been trying to work this out for 6 days now and i'm about to break something on half.

Can someone please guide me to right direction, maybe post some sample, anything.... please!

My old post regarding this:

How to update Sql Server related tables from Dataset(SchemaFill)

SqlDataAdapter.Update related tables

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

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

SUM col from multiple tables

I am trying to SUM a column from each of 3 tables, where an ID field is the same in each table and the date range is the same also It would look something like this: table1      table2      table3      table4  amt         amt         amt         t4ID  ID           ID           ID  date        date        date SELECT SUM(t1.amt),SUM(t2.amt),SUM(t3.amt) WHERE (each table).ID = @t4ID AND (each table).date BETWEEN @date1 AND @date2 the t4ID, date1 and date2 will be chosen by the user from controls and supplied to the statement via a parameter array. I have tried many variations ( with more to go) to send 1 @t4ID parameter, for example, and use that in all 3 tables (same with the dates). Any help to nudge me in the right direction would be appreciated. Thanks

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
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