Tracking Forums, Newsgroups, Maling Lists
Home Submit Tracker Forums
  Advanced Search
  HOME    TRACKER    ASP.NET


Advertisements:










SQL Server :: Delete Data From Two Table Using Left Outer Join?


i want to delete data from two table using left outer join..

here is my query:

delete from projects p LEFT OUTER JOIN emp_project empp on p.serial_no=empp.projectid where p.serial_no=7

but i am getting error:

Incorrect syntax near 'p'.


View 6 Replies (Posted: Mar 26, 2011 06:30 AM)

Sponsored Links:

Related Forum Messages For ASP.NET category:
SQL Server :: Trying To Return The Right Number Of Records Using Left Outer Join?
I have 9 tables i need data out of.. 8 of those tables will always just return 1 row of fields that i need. the9th table can contain many records.. So what i need is a few fields out of each of the 8 tables and then ALL records from the 9th table.. so i know that because there will be multiple records in the 9th table returned, that the fields i pull from the other 8 tables will contain duplicate information and thatsfine since i will be displaying the individual records from table 9 on their own page..So everythingcould be for the same vendor, except for thedate in the 9th table that contains specific dates and details about each record returned.When i review the execution plan im getting an yellow exclamation icon on a nested inner join and has this message as the warning: No Join Predicate

Here is what i have, but is returning incorrectly, way to may records, seems to be stuck in a loop.. then below that, i have one that returns the correct number of records, but everything except one column is duplicate, all the TR columns should be unique in the values stored in them.. the rest should all be duplicates, just because its the same vendor, so all the other information will be generic information about that record.

[Code]....

This FROM and WHERE return the correct number of records, but the fields are all duplicated except the last column with is transaction_date, seems that the 3 TR fields are being populated with the first records data or something.. not sure.

[Code]....

Posted: Aug 05, 2010 05:21 PM

View 3 Replies!   View Related
DataSource Controls :: Left And Right Outer Join In C# Using Linq?
How to use Left and Right Outer Join in C# using Linq with Eg.

Posted: Apr 20, 2010 10:25 AM

View 2 Replies!   View Related
ADO.NET :: Left Outer Join With Multiple Tables And Generic Lists In Linq And VB?
I'm having trouble doing a left outer join with multiple generic liststables. I've been researching for hours and there isn't a whole lot out there on this subject and VB (at least what I found).

I have the following Linq query:

Dim DataForGridview = (From s In S1 _
Join c In C1 On c.ID Equals s.ID _
Join v In V1 On v.ID Equals s.ID _
Select New With { _
.dataFieldID = s.ID, _
.dataField1 = s.dataField1, _
.dataField2 = c.dataField2, _
.dataField3 = c.dataField3, _
.dataField4 = c.dataField4, _
.dataField5 = v.dataField5 _
}).ToList()

S1 has a count of 30. V1 has a count of 20. C1 has a count of 10. This query joins the three but ends up with a count of only 10 based on the datakey ID and, of course, only shows those 10 in the gridview.

I would like to show all 30 from S1 in the gridview with blank fields wherever V1 and C1 do not have data. From what I've read I need to do a left outer join. However, all examples I've found are with two datasets.


I've tried multiple times expanding on what I've found by changing it to a group join and also adding the .DefaultIfEmpty() property in the query, but nothing is working.

How can I change the above query to a left outer join with these multiple tables?

Posted: Nov 16, 2010 07:28 PM

View 11 Replies!   View Related
DataSource Controls :: Where Inner Join Should Be Used And Where Outer Join Should Be Used?
where we should use inner join and where we use outer join with some easy and understandable examples.?

Posted: Dec 03, 2009 04:04 PM

View 1 Replies!   View Related
ADO.NET :: Forum Function Using Linq, Group, Distinct Left Join, Join?
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user,in each thread.
ere is my code... is returns the correct threads and some that should not be there.

[Code]....

Posted: Mar 24, 2011 04:54 PM

View 1 Replies!   View Related
Databases :: 'join' Text Files Similar To Sql Left Join?
I have some text files I need to parse in order to display my data, and what I have now are two text files with lots of redundant data. Instead of this I would like to replace the redundant data with a number referencing the text in another text file.

Posted: Jan 31, 2011 06:49 AM

View 1 Replies!   View Related
ADO.NET :: Put A Right Outer Join On Linq Statements?
I am trying to put a Right Outer Join on Linq statements. how to put Right Outer Join on Linq.

Posted: Aug 02, 2010 07:47 AM

View 1 Replies!   View Related
ADO.NET :: Use An Expression On A Linq Outer Join?
private void GetResults()

Use an Expression on a Linq Outer Join

Posted: Nov 18, 2010 01:16 PM

View 1 Replies!   View Related
DataSource Controls :: Delete Records Based On Join Condition From Other Table?
I want to delete recodrs from based on some join condition between two tables.

One table belongs to my DB ( table name : FeeDetails)

and second table ( TargetTable) is on different server.

I have delete records from TargetTable table based on join condition between two tables.

I am able to select records

[Code]....

Posted: Feb 25, 2010 04:36 PM

View 3 Replies!   View Related
DataSource Controls :: Left Join 2 Data Tables?
I have 2 different datatables. 2 datatables from 2 different servers.

My first datatable is this:

SELECT DISTINCT a.name, a.create_date, a.Modify_Date, a.Object_Id
FROM dbname.sys.objects a

and my 2nd datatable is the same, but from a different server connection string

SELECT DISTINCT b.name, b.create_date, b.Modify_Date, b.Object_Id
FROM dbName.sys.objects b

Basically, I want to compare which procedures are on one server and not the other (dev and prod)

So after I have my 2 datatables i want to create a data relationship on the name and then do a left join to see whats in a and not b.

Posted: Jan 29, 2010 06:14 PM

View 1 Replies!   View Related
DataSource Controls :: Outer Join In LINQ Getting My Desired Result?
I'm trying to achieve an outer join in LINQ but I'm not getting my desired result.There are two relational tables between Fruit_Name and Fruit_Ripe.The Fruit_Ripe table has a boolean field (IsRipe) that I use in my condition.The condition let me show all fruits that is not yet rippen (false) or fruits that isn't inputted yet in Fruit_Ripe table.

Dim fruit_salad = From myFruit In _DataContext.Fruit_Name
Group Join myRipeFruit In _DataContext.Fruit_Ripe On myFruit.fId Equals myRipeFruit.fId Into Group
From fruitty In Group.Where(Function(d) Not d.isRipe =

[code]...

Posted: Jun 26, 2010 04:01 AM

View 6 Replies!   View Related
DataSource Controls :: Clause Criteria Eliminating Records In Outer Join Query?
I know I'm missing something here but I can't figure out what it is. I've got a query joining three tables....accounts, payments, and a table linking the two (there is a M:M relationship).I'm trying to pull a list of all accounts in the account table that have a payment that needs to be resequenced, and also the maximum payment priority if there are any payments that haven't been fully paid. If all payments HAVE been fully paid, I want to return a 0.

It's that last bit of logic that I can't get right. If I include that in the where clause,I get only the accounts that have a payment that hasn't been fully paid. If I take it out, I get all the accounts I get, but I get the highest payment priority whether or not the payment has been fully met.

Hereis thequery....how do Iinclude the where clause criteria butstill include all accounts?

select distinct
bat.acct_id,
isnull(max(isnull(crt.pymt_priority, 0)), 0)[code].....

Posted: Feb 15, 2010 05:14 PM

View 6 Replies!   View Related
SQL Server :: How To Inner Join 2 Table With Primary Key By No Duplicate Data In Sqlserver 2008
How to inner join 2 table with primary key by no duplicate data in sqlserver 2008 ?

Posted: Nov 04, 2010 04:37 AM

View 5 Replies!   View Related
ADO.NET :: Left Join And Grouping In LINQ?
I have the below query which has left join and count in SQL. Please help me convert this SQL query to LINQ (using Entity frameowrk).

[Code]....

Posted: Aug 11, 2010 02:05 PM

View 10 Replies!   View Related
Timeout During Left Join Using LINQ To Entities?
I have the following LINQ query that does a left join:

var results = from v in ctx.dat_Visitor
join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
select new {

[code]...

My problem is that this LINQ query times out on me. I even upped the default timeout from 15 seconds to 120 seconds and it still times out. dat_Visitor has no more than 10 records in it, but vwSP (which is a view) has somewhere around 100,000 records. I know for sure that the timeout has to do with when I attempt to actually return sp2 in the results. If I change my LINQ query to not return sp2 in the results, then it is quick. Without sp2 the query looks like such:

var results = from v in ctx.dat_Visitor
join sp in ctx.vwSP on v.lu_Mentor.ID equals sp.ID into sp2
select new {
Visitor = v.Name,
Tot = sp2.Count()
};

Lastly, I'm using the 3.5 framework, but I am having serious thoughts about upgrading the website to 4.0, since I could then use DefaultIfEmpty().

Posted: Mar 2 at 23:59

View 1 Replies!   View Related
Forms Data Controls :: Gridview Showing Values From Two Tables Accommodation And Pic (left Join On Accommodation)
I have a gridview showing values from two tables accommodation and pic (left join on accommodation). If there is no pic uploaded I want to display the row without the pic. So in the gridview I first check to see if the picid is null, if it is i don't render any 'a href' code , if it's isn't i do.

The problem that i'm having is that instead of the image showing up in the gridview itemtemplate i get
','_blank','toolbar=no,menubar=no'))" >

Here is my .aspx code

SelectCommand="SELECT pic.picid, aspnet_Users.Username, accommodation.location, accommodation.type,accommodation.description, convert(varchar,accommodation.createdate,101) as createdate

Posted: Nov 19, 2010 04:33 PM

View 2 Replies!   View Related
DataSource Controls :: Left Join With Entity Framework?
how to do this query in EF1:

[Code]....

Posted: May 17, 2010 06:36 AM

View 3 Replies!   View Related
Access :: Access Does Not Seem To Recognize Full Outer Join?
I am using Access as my database for an ASP.NET project/Vb.NET. However it seems as though Access does not support Full Outer Joins.

Basically I have two tables in one query used to populate a Gridview. I need to list all available companies and their employee count even if that Company does not have any employees and return the result as 0. Is there anyway I can do this in an Access
query. The relationship layout is the tblCompany has a 1 to Many relationship with the Employee table.

Below is what I would like have happen however access does not seem to recognize FULL OUTER JOINS.

SELECT DISTINCTROW tblCompany.Comp_Name, tblCompany.Comp_CompanyID, tblCompany.Comp_CreatedOn, tblCompany.Comp_Status, Count(*) AS [Employee Count]
FROM tblCompany FULL OUTER JOIN tblEmployee ON tblCompany.Comp_CompanyID = tblEmployee.Comp_CompanyID
GROUP BY tblCompany.Comp_Name, tblCompany.Comp_CompanyID, tblCompany.Comp_CreatedOn, tblCompany.Comp_Status;

Posted: May 10, 2010 03:57 AM

View 1 Replies!   View Related
DataSource Controls :: How To Join Table In Stored Procedure Sql Server Db
my stored procedure is always extract some data into temp table such as"select name,address,content into #TEMP1 from table1,table2,table3"
finally, select another table together with #TEMP1 to extract all desire data from one queryselect T.name,T.address,T.content,C.other from #TEMP1 T, otherTable C
where T.ID=C.IDI would like to ask if i want to join one more table T2 into final query with "otherTable left C outer join on C.ID=T2.ID"

Posted: Jun 09, 2010 08:20 AM

View 1 Replies!   View Related
SQL Server :: Change Data Capture Records The Changes Like Insert, Update, And Delete To A Table?
CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism,Change data capture records the changes like insert, update, and delete to a table in SQL server.I have sql 2005 and I have created triggers and tables CDC to capture the data. everything functions good and the data is being updated in the _CDC tables. all i need to do now is to generate the updated data as a report (excel/html)- what should I do ......?i need to show only the updated columns when user selects the date periods ( Range between dates ) and the person name - i need to display any updated info about that person during that period. So this updated columns about this person should be displayed in excel formatted column wise.

Posted: Oct 26, 2010 09:39 PM

View 2 Replies!   View Related
SQL Server :: Delete Objects From Table Using Another Table To Select Rows?
I have one table named 'dbo.ac_Products' Within this table there is a column named 'ProductId' There is also another table named 'dbo.ac_CatalogNodes' Within this table there is a column named 'CatalogNodeId' and a column named 'CatalogNodeType'

I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1

Posted: Sep 14, 2010 01:23 PM

View 4 Replies!   View Related
C# - Change Data Binding From Left To Right In Table?
Normally Table data bind from top to bottom with column headers at the top of the table.Is there way to change the data binding from left to right and headers in the first column of the table?

Posted: Feb 20 at 7:06

View 1 Replies!   View Related
SQL Server :: How To Put Restriction On The Table That No One Can Delete The Table
I am deploying the solution on the server i want no user can deleted the table by using the sql injection, i want to put restriction on the table that no one can delete the table. in short drop table should not execute.

Posted: Jan 05, 2011 01:44 PM

View 5 Replies!   View Related
Forms Data Controls :: Inner Join An Image With A Different Table And Display It In A Gridview?
I'm trying to inner join an image with a different table and display it in a gridview. I'm getting the error:

DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name 'imageID'.

I do understand that I'm not selecting imageID in my select statement, and that's what's causing it.

Usually to get my image I have a handler.ashx and in my form I do: SELECT * FROM Images. In the source I do:

<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# "Handler.ashx?id=" + Eval("imageID") %>'
Height="150px" Width="30px" />
</ItemTemplate>

That's what I'm trying to do now, but obviously it's not going to work.

In Staff.aspx.cs:

[Code]....

Posted: Nov 05, 2009 09:12 PM

View 1 Replies!   View Related
Forms Data Controls :: Databind Refresh - Records Left In The Table Andgoes To The <emptytemplate> ?
Ihave a gridview, and I set the datasourcein page_load to a linq query. Ihavedeletelinkbutton in the grid andam writing code in gridview1_rowdeleting event to deletethe record from the table and all its records in child tables. Works fine, except whenI databind() after the deletes to refresh the grid, it thinks there are no records left in the table andgoes to the <emptytemplate>. There are in fact many other records that should appear. When I go out of the gridview and come back in, everything is fine. The proper records have all been deleted, and the remaining ones show. Here is the code. the table being displayed is Schedule. It has child records in the two other tables.

[Code]....

This is from Page_Load

[Code]....

Posted: Feb 09, 2010 02:44 PM

View 4 Replies!   View Related
Forms Data Controls :: Cannot Delete Data - How To Delete Data In A Table
check this link: [URL]

Can not delete data. How do I delete data in a table?

Posted: Jul 18, 2010 10:17 PM

View 3 Replies!   View Related
SQL Server :: Join A Table With PG_Table, Instead Of A Use Within PG_Table?
Can I make, join A Table with PG_Table, instead of A use within PG_Table

WITH PG_Table AS (SELECT 'TotalRecords' = COUNT(1) over (PARTITION by 1), 'RowNumber' = ROW_NUMBER() OVER(ORDER BY TableId)

Posted: Dec 02, 2010 12:18 AM

View 5 Replies!   View Related
SQL Server :: How To Delete From Parent And Child Table Using Sql Datasource
i am trying to delete using Gridview from child first and check if there is no child to delete in parent table i am using sql datasource in the delete tab in sql datasource i put DELETE FROM table1 WHERE (IDtable = @IDtable) IF (SELECT count(*) FROM table1 WHERE parent_dtl_id = 66) =0
BEGIN delete from parent_dtl where order_dtl_id= 66 END

i deleted all items,deleting from 2 tables child and parent was successfully on the last row from gridview but i have this error on my page on the last delete Specified argument was out of the range of valid values. Parameter name: value

Posted: Dec 02, 2010 08:05 PM

View 2 Replies!   View Related
SQL Server :: Delete Comma From Table Field Values?
I have a table in which there is one column named "Address1"

Now in this column all the data in this displaying as follow:-

sector-2, Guj,676
Sector-4,AHD,8989

Now i want to modify all the data in this column,

if there is any "," comma found in the data,

it should delete that comma from the data?

Posted: Jan 07, 2011 07:52 AM

View 2 Replies!   View Related
SQL Server :: Delete Entry Directly In Any Aspnet_* Table?
if It is okay to delete entry directly in any aspnet_* table ? because i screwed something.

Posted: Jul 29, 2010 01:47 AM

View 3 Replies!   View Related
SQL Server :: Get All Data From Tables When Inner Join Is Not Enough?
Suppose I have two tables "t1" and "t2"

t1 :
id FirstName Age
1 Joe 22
2 James 33
3 Bart 28
t2:
id LastName
1 Coymer
2 Manes

I can combine the data from the two tables using an inner join.

1 Joe 22 CoyLap
2 James 33 Manes

The problem though is that the person "Bart" with an Id of 3 is left out because he does not have an entry in the second table.So how can I include Bart in the output in such a way that the output might look like this:

1 Joe 22 CoyLap
2 James 33 Manes
3 Bart null null

Or spaces can instead appear for Barts last name and age instead of null.Basically I just need Bart in the output as well even though he has no age and last name.

Posted: Sep 28, 2010 11:26 PM

View 3 Replies!   View Related
SQL Server :: XML With PrimaryKey Values To Delete Rows In Database Table At A Time?
I'm having a gridview in my aspx page with checkbox,User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in case) to a serializableclass and generating an XML and sending it as parameter to the Stored Procedure.

My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??

If it works, I can dobulk update with XML only, i already did bulk insertion by generating an XML..

My Another Question is, Will it affectthe performance? Each time when i construct an DTO object and adding itto the Generic List??

WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method?

My XML will be like this

<root>
<Customer>
<CustomerID>GUID1</CustomerID>
<CustomerID>GUID2</CustomerID>
<CustomerID>GUID3</CustomerID>.....

Posted: Oct 25, 2010 05:56 PM

View 4 Replies!   View Related
ADO.NET :: Join 3 Table In LINQ?
i use Default membership provider for my membership

i add a profile table for some more information from user and it related with UserId with aspnet_Users

to get datafrom my database i use Entity Data Model

now i want to write a Linq to get data from all 3 tables

Posted: Oct 10, 2010 06:39 PM

View 1 Replies!   View Related
Return Multi-table Join Value From BLL?
This question is regarding the ASP.NET webservice that i am creating using the DAL-BLL architecture for my final school project.I have a stored procedure, which is a select query with an inner join for 2 tables.Hence the stored procedure returns multi-table value. One of my DAL tableAdapter methods accesses this stored procedure. How do i retrieve the return value in the BLL? Do i have to create a class structure similar to the one supposed to be returned by the stored proc? or is there a direct way to achieve the same?
Here is some more information:I am using the SQL dataset (.xsd) in DAL. So i have a datatable called "Insurance", which has a tableAdapter. One of the queries in the adapter references to a stored procedure, which has an inner join. So my SP looks like:

ALTER PROCEDURE dbo.GetInsurancesPaged
(
@startRowIndex int,

[code]...

Posted: Jul 26 10 at 3:43

View 2 Replies!   View Related
DataSource Controls :: Possible To Join With A #Temp Table?
I can join with a regular SQL table fine but this won't work:

SELECT normalTbl.*, tmp.fld1FROM normalTbl
LEFT JOIN #Temp tmp ON tmp.joinID = normalTbl.joinID

Alltmp.fld1 values areshown as NULL even though fld1 contains valid values and the joinID matches the normalTbl (when Icall SELECT * from #Temp). If I do the same exact thing with a regular table, fld1 shows the correct value. What am I doing wrong?

Posted: Feb 13, 2010 06:13 AM

View 4 Replies!   View Related
Forms Data Controls :: Delete A Row From A Table?
I want to delete a single row where the title =Text1.Text

using MS Access Database, And C# in ASP.Net

Posted: Oct 13, 2009 12:55 PM

View 6 Replies!   View Related
MVC :: To Create View From Linq (subsonic) Table Join
i am struggling to work out how i can create a strongley typed view from my linq statement that joins 3 tables together
i have created another class and imported my view into that class, but when i go to create a view it is missing from my dropdown list of

view data classes. i think i am on the right track but must be missing something to turn my class into a view data class

here is my code: [Code]....

Posted: Sep 03, 2009 08:27 AM

View 4 Replies!   View Related
SQL Reporting :: Dynamically Change Left And Top Properties Of Table?
How can I change Left and Top properties of Table control in a .rdlc file from code behind at Run-Time?

Posted: Apr 04, 2010 07:32 PM

View 2 Replies!   View Related
DataSource Controls :: SQL Select Many From Table Inside Inner Join
I have this code that only returns the first row from table c: I want to get all items from table c.

public DataTable GetData()
{
string sqlQuery = "SELECT * FROM a INNER JOIN b ON (b.userId = b.UserId) INNER JOIN c ON (a.listId= c.listId) ORDER BY date DESC ";
sqlCmd = new SqlCommand(sqlQuery, sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter a = new SqlDataAdapter(sqlCmd);
a.Fill(dt);
sqlConn.Close();
return dt;
}

Posted: Dec 05, 2009 05:01 PM

View 3 Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved