when I want to get a value by 3 tables, how to join 3 tables?

I'm doing DTS and need to write a SQL Query for what data have to move and what data have to deleted

I have many tables need to difference query. this one is one of tables

Table-Account0

column   EAIndex  、BillerCode、ChainCode、PostDate、PostAmount、BalanceDate、BillBegDate、BillEndDate、BillCount、ChAmount

 Table-Biller

 CompanyCode、BilerCode、BillerName、UniformNo、BusCode、AcctNo、BillerInfoList

Table-DataBackup

column CompanyCode、Keepmonth

when I'm doing SQL DTS, in Table Account0 , I need to select the data which BalanceDate is < today's Date-6

---------------------------------------------------------------------------------------------------------------------

my sql query is below . but it didn't works , I must did somehing wrong with this query pleae point me to correct it

 SELECT         ZT_EAccount0.*, ZT_DataBackup.KeepMonth
FROM             ZT_EAccount0 INNER JOIN
                          ZT_Biller ON ZT_EAccount0.BillerCode = ZT_Biller.BillerCode CROSS JOIN
                          ZT_DataBackup on ZT_EAccount0.BalanceDate < getdate()-6

 

----------------------------------------------------------------------------------------------------------------------- 

thank you very much

 

0
jcjcjc
7/19/2007 6:17:54 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
643 Views

Similar Articles

[PageSpeed] 23

i think Left Outer Join Should be there.,

 

 
SELECT ZT_EAccount0.*, ZT_DataBackup.KeepMonth
	FROM ZT_EAccount0 
	LEFT OUTER JOIN ZT_Biller ON ZT_EAccount0.BillerCode = ZT_Biller.BillerCode 
	LEFT OUTER JOIN ZT_DataBackup ON ZT_Biller.CompanyCode = ZT_DataBackup.CompanyCode
                WHERE ZT_EAccount0.BalanceDate < getdate()-6
 
hope it helps./.

Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/19/2007 6:47:40 AM

thank you , your query is works. but the result not correct

it sesms didn't do where ZT_EAccount0.BalanceDate< getdate()-6

is shows all data from tables..

 

thank you again

0
jcjcjc
7/19/2007 7:00:30 AM

jcjcjc:
where ZT_EAccount0.BalanceDate< getdate()-6

where ZT_EAccount0.BalanceDate < DATEADD(DD, -6, GETDATE())

hope it helps./.


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/19/2007 8:43:58 AM

thank you , SQL Query works. but the result still looks weird..

ZT_EAccount0.BalanceDate have a column value 20061031

when query -- DATEADD(MM, -9, 20070719)  the result should be 20061119

 

so where ZT_EAccount0.BalanceDate < DATEADD(MM, -8, GETDATE())  -- subtract 8 months

will shows data which ZT_EAccount0.BalanceDate  < 20061119

 

it shouad have the column value 20061031 appears , but it didn't

does anything wrong still?

 

thank you very much

0
jcjcjc
7/19/2007 10:09:32 AM

jcjcjc:
20061119
 

what is the type of BalanceDate?


Thanx,
[KaushaL] || BloG || Profile || Microsoft MVP

"I would love to change the world, but they won’t give me the source code"


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and mark your thread as Resolved for the sake of Future Readers.
0
kaushalparik27
7/19/2007 10:34:46 AM
Reply:

Similar Artilces:

moving from one table to other table automatically for every 3 months by checking the paticular value of the table field
Hi   I am having a table called as status ,in that table one field is there i.e. currentstatus. the rows which are having currentstatus as "ticket closed",i want to move those rows into  other table called repository which is having same table structure as status table. I can do programatically. but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?   Please help me.   Thanks. Hi, check http://msdn2.microsoft.com/en-us/library/aa902640(SQL.80).aspx is SQL2000 for 2005 check SSIS &nb...

join 3 tables sql query
I am going to post my sql query below. But let me explain that It works except for the "Company" results. For "Company" it doesn't recognize "Paid= 'true'", Because on the second query "select case = Paid Invoices" it returns Company invoices which are not paid. But it does fine with Client invoices. Does that makes sense? I will post the code and you tell me what you think: (this sqldatasource is populating a gridview.Select Case ddlInvoices.SelectedItem.Text            Case "Open Invoices"  &nb...

SQL join with 3 tables on FireBird database
Hello, I have a query that works like select a.*, b.* from taba a, tabb b where a.f1 = b.f2 Now I need a join to this result Does anyone have an idee an idee how to do this Thanks in advance Herman "Herman vanRuitenbeek" <herman@re-tech.nl> wrote in message news:55278@forums.codegear.com... > > I have a query that works like > select a.*, b.* from taba a, tabb b > where a.f1 = b.f2 > > Now I need a join to this result > > Does anyone have an idee an idee how to do this You can join as many tables as you need as long as you...

joining 3 tables
The following stored procedure joins orderline table with order head tableALTER PROCEDURE dbo.SP_GetAllOrderDetails @OrderID int as begin select TblOrderHead.OrderID, DateDue, DateDespatch, TblOrderLine.ProductID, Quantity from TblOrderHead inner join TblOrderLine on TblOrderHead.OrderID = TblOrderLine.OrderID where TblOrderHead.OrderID = @OrderID end ======================================== i now want to join each row that is returned to the product table, taking .. 'title', and 'description' from it, selected by the product IDs that have been returned in the sp abov...

How to join 3 Tables
Table MedicineDate             PigNo            Medicine No   Price061112            A                       A              3070104            B                       C       &nb...

how to join 3 tables?
helooo... i have 3 tables -Recipes, Ingredients and Products. Recipes table: RecipeID -PK Ingredients table: IngredientID -PK IngreRecipeID -Foreign key to Recipes table IngreProductID -Foreign key to Products table Table: ProductID -PK how do i join these 3 tables? SELECT * FROM REcipes INNER JOIN Ingredients ON Recipes.RecipeID=Ingredients.IngreRecipeID INNER JOIN Products ON Ingredients.IngreProductID=Products.ProductIDStarting with ASP.NET 2.0? Look at:Programming Microsoft Web Forms My Blog...

Filtering Data For A Table With Given Values (Not Table Values From a Referenced Table)
I need to filter the records displayed for a table with a value provided in a textbox.  I am using a Dynamic Data Entities Website with Visual Basic Programming. Does anyone have a simple example to follow? Thanks for any advice. The only thing I know of without using the 4.0 Preview stuff is Josh Heyes project on codeplex Dynamic Data Filtering you can have a look at my tutorial on how to use it (c# I'm sorry) Dynamic Data Filtering – InstallationSteve Seeking the elegant solution.[Oh! If olny I colud tpye!]c# Bits blogOh, and don't forget to mark as ans...

Wanting to take info from one SQL table and update to a seperate SQL table
Anyone have an idea of how I can take information from one table and edit it and insert into another SQL table.  I am using stored procedures and would like to continue to do so. Here is a copy of what I have so far:   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="mp_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </div> <h2><strong><font color="#C00000">Please enter a valid account number:</font></strong><...

Retrieve identity number of child table and then inserting data simultaneously in 3 child tables and 1 parent table
Hello, I am new to Db and .net worl. This is my first application, so my approach might be wrong correct me if i am. I have about 10 tables, in db. Main table is a user table with 1-PK and 3-FK. I have to insert data into 1-parent tables and 3-child tables using a form. How shud i do it? The approach i am taking is: Retrieve identity of child tables and then insert those values into parent tables. Also the FK in parent table are PK's of the child tables. First of all i made a small code to retrive the identity of one table, but its not working. Here is the code: form.aspx  <%@...

converting 3 database tables to 1 table
Hello, i've got a problem with receiving data from my database. I've got 3 tables: Log & LogDetail & Categorie 1 log corresponds with 12 LogDetails. Each logDetails corresponds with one Categorie Table LOG: Name Type Description PK FK ID ID Int Unique nr. from log X   X ServerID Int Reference to a server   X   Datum Datetime Date from log       Table LogDetail : Name Type Description PK FK ID ID Int Unique nr. fro...

3 dropdownlist bound to 3 differents tables
Hi all I have a form which I want to put 3 dropdownlist : the first one will be bound to the living city (tb_cities), the other one will be bound to the nationaly (tb_nationality) and the third one regards leisure (tb_leisure). those 3 tables are not link together. therefore how can i populate those 3 data in the same form : name : firstname : date of bird : nationality : (here the dropdownlist given the choice of the tb_nationality) living place : (here the dropdownlist given the choice of the tb_cities) leisure : (here the dropdownlist given the choice of the tb...

Treeview Datawindow with 3 Tables. How to update all tables
DW.NET 2.0 I have a datawindow treeview with 3 Tables. How can I update ( per programm-code if needed ) all three tables? Is there any example available ? Thanks Peter You would: 1. Issue the UpdateData statement with the flag that tells the datawindow not to clear the update status flags (and check the return code to ensure that it worked) 2. Dynamically modify the update specifications for the DataWindow to point to the next table (modify both the table to be updated and the columns involved, including which are key columns). 3. Repeat steps 1 and 2 until a...

Save in father table and children table #3
Hi How do I building a Stored Procedure that save in father table and after save in 2 children tables, but I want use commit and rollbackm if someone error hapen FATHER / \ / \ CHI1 CHI2 Please send me example This is a multi-part message in MIME format. --------------A7C4309166ED083C8B8B75E8 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Please, send it to ase newsgroup this newsgroup is dedicated to EAServer. Leon. mutley wrote: > Hi > > How do I building a Stored Procedure that save in father table an...

Getting a value from a SQL table displaying a specific page based on that value
May seem to newbie but here goes:I have a login page.  For the login I run a stored procedure with 4 return values, 0 - being valid, 1 - username exists already, 2 - username is incorrect3 - password is incorrectThis all works fine.  But from that I have the default page when the login credentials are correct.  The issue is I need to display a specific default page based on their location id from the member table which I already run a sp against for the login.  I have set up an .ascx page with a datagrid to display the results which works when I run the query for a h...

Web resources about - when I want to get a value by 3 tables, how to join 3 tables? - asp.net.sql-datasource

Table tennis - Wikipedia, the free encyclopedia
Table tennis , also known as ping-pong , is a sport in which two or four players hit a lightweight, hollow ball back and forth using table tennis ...

Central Australian table grape season looking good but rain and competition threatens
Central Australian table grape season is on again and things are looking good for a farm in Ti Tree.

Philly Papers Set the Kobe Table
As usual, the Philadelphia Daily News has the killer-pun front page headline. But in terms of must-reads ahead of Kobe Bryant’s final visit ...

Get a VIP table at the hottest club in town with Tablelist
If you love going clubbing and always try to hit the swankiest spots in your city, then this new app is for you. Tablelist gets you access to ...

Laying My Cards on the Table
Steven Hayward at the wingnut PowerLine blog indulges in a silly hypothetical: GUNS: TIME TO CALL THE LIBERAL BLUFF? Here’s an idea: let’s ...

Refugees Welcomed at My Thanksgiving Table
... Laos. Once, on Thanksgiving, I started a conversation asking everyone's earliest memories. She spoke last. She remembered eating at a table ...

Lawler: Preliminary Table of Distressed Sales and All Cash Sales for Selected Cities in October
Economist Tom Lawler sent me a preliminary table below of short sales, foreclosures and all cash sales for a few selected cities in October. ...

Survive awkward moments at your Thanksgiving dinner table
What's the best way prevent fights over subjects like politics and religion, or even your love life?

Motorized Picnic Tables Are Making Australian Cops Crazy
Next time someone mentions an Australian picnic table, let there be no doubt as to what they’re talking about. A group of nine guys from Perth ...

Draghi: Risk of deflation 'firmly off the table'
European Central Bank President Mario Draghi spoke at the Economic Club of New York on Friday.

Resources last updated: 12/4/2015 8:33:10 PM