DELETE Query Syntax Using Subqueries

Jul 20, 2005

I have the following SELECT query, the results of which I would delete
from the table they're pulled from:

SELECT A.* FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)


However, when I try to run the following query, I get an error ("Msg
170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):

DELETE FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)


What am I doing wrong, and how do I resolve the issue? Any help is
greatly appreciated....... thanks in advance!



-=Tek Boy=-

View 6 Replies


ADVERTISEMENT

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

Subqueries Do Not Follow Master Query GROUP BY Rule

Nov 20, 2011

I have a query with 2 subqueries, and no error message is reported, but, my problem is that the 2 subqueries do not follow the GROUP BY rule and show the total instead of by vendor...

Code:
SELECT Table1.agents AS Vendor
, Count(Table1.carS) AS Car_Sold
, Sum(Table1.carP) AS Car_Price
, Count(Table1.busS) AS MortBus_Sold
, Sum(Table1.busP) AS busPRice

[Code] ....

View 1 Replies View Related

DELETE Where Syntax ... Need Help :)

Jun 28, 2007

I have a table with the following columns,NAME, TYPE, TAGAnd there may be 'duplicates' on name and type.How can I delete them??I want to delete all with duplicate NAME and TYPE

View 7 Replies View Related

Help With Delete Group Syntax

Jan 12, 2007

Here are my 2 colums of my table;

ID LastContact
1a 4/12/2003
2d 5/12/2004
1a 9/12/2006
3c 6/12/2005

...and so on

I need to clean up this table so that I have one unique ID, with the latest 'Last Contact' date....so for this example, after the delete query I would only have '1a' with the '9/12/2006' LastContact colum..and the other two rows would be unaffected, since they didnt have a duplicate id..so after the delete query, my table would look like this;

ID LastContact
2d 5/12/2004
1a 9/12/2006
3c 6/12/2005

How would I write such a query...some sort of grouping ?

View 3 Replies View Related

Multiple Row Delete Syntax Error

Feb 2, 2007

Hello
I am trying to delete multiple rows in my sql 2000 database, I have used the following sysntax but I keep getting errors:
DELETE From NameList
WHERE LName = 'Smith';and LName = 'Jones';and LName = 'Peters';and LName = 'Adams';and LName = 'Conner';and LName = 'Simon';
I have tried editing the syntax in a variety of ways, but I just can't find the correct solution. 
I have Googled and so far not found another syntax format.  What am I doing wrong.
Thanks.
Lynn

View 7 Replies View Related

On DELETE On UPDATE Cascade Syntax Error

Dec 13, 2006

Hello
I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.  However, I have having problems.
I have already created  the tables with primary keys on each table and foreign keys linking each table to the next.
I tried to delete a row from the parent table and was given this error:
DELETE FROM [dbo].[DomNam]WHERE [DomNam]=N' football '
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.
 
I tried to insert an alter table query:
ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_ID
REFERENCES dbo.Dom_CatA (Dom_ID)
ON DELETE CASCADE ON UPDATE CASCADE
But on Execute I saw this error:
Error]  Incorrect syntax for definition of the 'TABLE' constraint
What is wrong with the above syntax?
Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables. 
I would be grateful for any advice.  Thanks.
 
 
 

View 8 Replies View Related

Delete Sql Syntax For Linked Notes Table

Apr 17, 2006

I am hoping this is a quick easy question for someone! :)I am trying (struggling) with moving data from Sql Server to a LotusNotes table.I am using SQL Server 2000, I have a Lotus Notes linked server (usingNotesSQL), and I wasnt to clear the table (delete all records) and thenreload it from my data on SQL Server.What is the syntax to delete the records?My select statement would be like this:select * from openquery([LinkedServer], 'select * from NotesTable')where lastName='Smith'My delete statement ??? -- cant quite figure out the syntax of thisone....select * from openquery([LinkedServer], 'delete from NotesTable') wherelastName='Smith'(this doesnt work)Thanks!

View 9 Replies View Related

I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?

Nov 26, 2007

this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
 
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
 
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you

View 1 Replies View Related

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question) 

View 2 Replies View Related

Subqueries

Apr 28, 2004

Trying to find which Employees have NOT entered a record (timecard).

Table #1 Employee_Hours
Table #2 Employee (List active)

Table #1
SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding
FROM Employee_Hours
WHERE ([Date] Between '1/1/04' AND '12/31/04')
GROUP BY EID, DATEPART(wk,[Date])
HAVING MAX([Date]) = '1/24/04' --Pay period

Table #2
SELECT EID, DisplayName
FROM Employee
WHERE (Active=1)

I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.

Thanks

View 1 Replies View Related

Use Of Subqueries

Jun 15, 2004

Hi,

I have the following sql statement:
INSERT INTO prg_lvl VALUES (
'Data Recovery',
'Data Recovery',
7,
'ENG',
0,
16,
'Menu=w_data_recovery_stk;',
( select max(menu_id) + 1 from prg_lvl ),
'K');
that works for Sybase and I wanna make it works for SQL Server.

The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).

Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem

Thanks,
fady

View 3 Replies View Related

Subqueries

Oct 14, 2005

Hi,

I would like to know

What is Corelated SubQueries??

Thanks in Advance

View 3 Replies View Related

Subqueries

Jan 14, 2008

I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.

For instance, I have the following code, which lists all orders for the month of November, 1996:

SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;

I have been asked to do the same thing, but using a subquery. I came up with the following:

SELECT CustomerID, CompanyName FROM Customers a
WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;

However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?

Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)

View 16 Replies View Related

Are Sub-subqueries Possible?

Apr 12, 2006

Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon

View 2 Replies View Related

Help With SubQueries?

Jul 20, 2005

Guys,Got this query...---------------------------SELECT TOP 5 Tbl_admin_hotels.HotelName,(SELECT COUNT(Tbl_marketing_history.HotelID)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS CountTotal,(SELECT MAX(DateSent)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS LastSentFROM Tbl_admin_hotelsWHERE NOT Tbl_admin_hotels.HotelID = 99ORDER BY CountTotal DESC---------------------------Within the table Tbl_marketing_history there is also a 'Subject' column thatI require, the row to grab this Subject column from should relate to the rowI'm selecting in the SubQuery to grab 'DateSent'.I've tried and tried to grab multiple columns but failed miserably? Cananyone help pleaseeee!Cheers, Ash

View 12 Replies View Related

Should I Do Two Subqueries?

Sep 27, 2007

In this stored procedure, I want to grab all the information for Sales Representatives that made less then 6 visits. I have done that in the subquery. But I also want to be able to display their visits by Dates where any number of visits was made. Meaning, i want to be able to show Everyday where a visit was made, but i only want the records that are less then 6 visits. So I want nulls to show up for the dates where no visits were made. So I can track the people that didnt make visits, on days other made visits (which means they should have visits too)

So basically my report looks like this:
09-10-2007 09-11-2007 09-14-2007
Tom Smith 1 3 4
Janice Cooper 3 5 5
Troy Slater 5 4 2
Kelly Short 4 4 4


When i want it to look like this:

09-10-2007 09-11-2007 09-12-2007 09-13-2007 09-14-2007
Tom Smith 1 3 null null 4
Janice Cooper 3 5 null null 5
Troy Slater 5 4 null null 2
Kelly Short 4 4 null null 4

Its an exception report, so i want to show what was not done , on days that are supposed to have things done, i cant show this, if dates are not displayed before there were no visits less then 6. I want to display all relevant dates, where any number of visits was mad. But only show the visits that were less then 6.

Here's my stored procedure that already has the less then 6 visits showing. But doesnt always show the dates. What should i do?






Code Block
SELECT dbo.Qry_Visits.Status,
dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],
B.VisitsTotal

FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status
FROM dbo.Qry_Visits
WHERE Qry_Visits.Status=2
GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status
HAVING SUM(Visits) < 6)B
ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND
CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE Qry_Visits.Status=2
ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

View 5 Replies View Related

Using Subqueries

Dec 23, 2007



Hey everyone, I'm running into an issue where I have a select statement that retrieves all employee records from a employee table. In this statement I'm displaying first and last name, titlte and the employees manager id(which is the employees id). What I want is a query that instead of displaying the manager's id, display the manager's name. The query that I'm using below doesn't work. I was hoping that someone could help me out:
SELECT LastName, FirstName, Title, Extension,
(SELECT FirstName, LastName
FROM Employees
WHERE (ReportsTo = EmployeeID)) AS Reports
FROM Employees

Here's the data that I'm using:
LastName, First Name Title Extension Reprots To




Davolio
Nancy
Sales Representative
5467
13

Fuller
Andrew
Vice President, Sales
3457
0

Leverling
Janet
Sales Representative
3355
12


When I run this query I get a error saying "Only one expression can be specified in the select list when the subquery is not introduced with Exists"

Thanks in advance for your help.

View 5 Replies View Related

SQL Query Syntax

Nov 30, 1998

I recently wrote a query similar to this:

Select e.name, e.employee_id, d.department_name
from employees e,departments d
where e.department_id = d.department_id

An associate recommended that I change this query to add the words INNER JOIN like this:

Select e.name, e.employee_id, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id

I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster.

Would anyone like to confirm this?

View 2 Replies View Related

Please Help Me With The Syntax For This Sql Query.

Aug 29, 2007

Hi.. I want to join this query


Code:

select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & "
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0


in this query


Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID


The Place where i need to Join is after
Code:

iCalls_Status.Status_ID,iCalls_Status.Status_Label

and before
Code:

((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID )


I want to add ( / ) in between these 2 queries. The reason is for example first query will return '5' and second '10' , so the output i need is 5 / 10. And i need to put this query in a variable (Countrec) like

Code:

select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec


The Final Query would be something like this

Code:

select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D,
iCalls_Status.Status_Label, select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ( / )
select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0 as Countrec from
((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on
iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID

but this syntax is not correct..Please can U get me the Correct Syntax.

View 7 Replies View Related

SQL Query Syntax Help

Jan 13, 2005

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!


code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------

View 1 Replies View Related

Query Syntax Help

Jan 13, 2005

I have a query that I am running out of sql server 2000 that is pulling duplicate records. I can probably figure it out but I am wondering if someone could look at it and point out errors in my syntax and/or structure.

Thanks!


code:--------------------------------------------------------------------------------
SELECT p.ParticipantID, pr.RaceID, p.FirstName, p.LastName, pr.Bib, p.Gender, pr.Age,
pr.AgeGrp, p.DOB, p.Address, p.City, p.St, p.Zip, pr.Clyde, pr.WhlChr, pr.RcWlk,
p.Phone, p.Email, reg.ShrtSize, reg.ShrtStyle, reg.WhereReg, reg.DateReg, reg.AmtPd
FROM Participant p INNER JOIN PartReg reg ON p.ParticipantID = reg.ParticipantID JOIN PartRace pr
ON pr.ParticipantID = p.ParticipantID JOIN RaceData rd ON pr.RaceID = rd.RaceID
WHERE (rd.EventID = 45 AND pr.RaceID = reg.RaceID) ORDER BY p.LastName
--------------------------------------------------------------------------------

View 7 Replies View Related

Query Syntax Plz

Feb 6, 2008

am looking to make a select query that has some condition where it should look like:
select if(SomeField is null) then 'Not Set' else SomeField
from SomeTable

how it can be done ?????

View 9 Replies View Related

Query Syntax

Nov 15, 2007

Hi guys<


I wan to know what command can I use to view the latest date of posting of interest in my database given the following tables

Table: DepositInterest
Fields: code1, code2, interestdate

code 1 and 2 are primary keys.
Then I tried to run a query using where clause but then it happened that all interestdate are viewed.

What I want is to just view the latest interestdate.

View 4 Replies View Related

Sql Query Syntax

Jul 23, 2005

Hi all,I have the following tables:T1==ID Name-- ----1 Name12 Name2T2==ID Color-- ----1 Color11 Color22 Color22 Color3and I would like to get the following output (without duplicates):ID Colors-- ------1 Color1, Color22 Color2, Color3Is it possible?

View 4 Replies View Related

Query Syntax Help

Sep 17, 2005

Hello All,I have the following table:CREATE TABLE [dbo].[TBL_NAME] ([NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[STANDARD_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GOWith values:insert into tbl_namevalues('DAN', 'DANIEL')insert into tbl_namevalues('DANNY', 'DANIEL')insert into tbl_namevalues('DANYY', 'DANIEL')Question is:I need want to construct a query which returns all names for a standardname plus the standard name itself.e.g.if name = 'DAN' then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'ff name = 'DANIEL', then return 'DAN', 'DANNY', 'DANYY', 'DANIEL'i have the following sql:declare @name varchar(50)select @name = 'DANIEL'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----declare @name varchar(50)select @name = 'DANNY'select standard_name from tbl_name where name = @nameunionselect name from tbl_name where standard_name = (select standard_namefrom tbl_name where name = @name)unionselect name from tbl_name where standard_name = @nameunionselect standard_name from tbl_name where standard_name = @name----Both appear to work fine..can anyone see a fault or suggest a cleanerway to achieve the above ?Suggestions/pointers appreciatedThanks in advance

View 3 Replies View Related

SQL Query Syntax

Jan 20, 2007

sql query syntax?i write this query but error get : column not foundhow can i write ?SELECT c.stock_cat_name, a.stock_code, c.description, a.date,IIF(MID(a.type,2,1)="I",SUM(a.quantity),0) AS iqty,IIF(MID(a.type,2,1)="O",SUM(a.quantity),0) AS oqtyFROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_codeWHERE a.date>=[from_date] And a.date<=[to_date]GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,MID(a.type,2,1)ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);this database driver sage line 50 odbci write a simpale query of this database, successful run but when iwrite in condition (IF) is not run error comes : column not found.rashesh

View 2 Replies View Related

Query Syntax

Sep 11, 2006

Hi all! I am new here and I'm also new to SQL.. I hope somebody could help me regarding my problem.

I don't know if this is possible but I would like to have a code that
can integrate data from 3 tables. The names of my tables are Savings,
Loans and Insurances. Their common field is the MemberID. One member
could have zero or more Savings Accounts. At the same time, a member
could also have one or more accounts on Loans or Insurances.

How can I get the data that would appear like this:



MemberID - Savings Account - Loan Account - Insurance Account

0001
-
S0001
- L0002

- I0001


-
S0003
-
L0005
-

0002
-
S0012
-
-

0003
-
S0004
-
- I0002


-
-
- I0003







I'm using MS SQL Server 2005.

Hope you guys could help me with this. Thanks a lot, in advance!

View 6 Replies View Related

Help With Query Syntax

Aug 2, 2006

Hi all, I have been fighting with this query and would like some advice. Please consider the following tables;

prod_table
widget_number
shift
date
production_time (in minutes)

down_table
rec_id (ident key)
down_shift
down_date
down_minutes

Prod_table (data)

widget_number shift date production_time
0001 1 08/02/06 5.00
0002 1 08/02/06 10.00
0003 1 08/02/06 7.00
0004 2 08/02/06 5.00
0005 2 07/31/06 3.00

Down_table (data)
rec_id down_shift down_date down_minutes
1 1 08/02/06 3.00
2 1 08/02/06 20.00
3 2 07/31/06 10.00

I would like to combine the production times and down times into one summary where the down time is in the same date and shift as the production time.
As you can see in my results below, I can group them correctly, but the down totals obviously repeat for each match. Is there any way of getting to the "Desired results"?

My Results

widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 23.00
0003 1 08/02/06 7.00 23.00
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00


Desired results

Widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 NULL
0003 1 08/02/06 7.00 NULL
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

thank you in advance.

View 10 Replies View Related

Aggregates Containing Subqueries...

Sep 20, 2007

So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.
Anyways, in my select I have something like this:
sum( case when code in (1,2,3,4) then 0 else 1 end ) as total
which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:
sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total
because apparently you can't use selects or aggregates within an aggregate.
So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.
Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).

View 4 Replies View Related

Effectively Using Subqueries

Dec 23, 2005

Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.

My SQL statement:

Select * FROM tblData WHERE LastName=
(SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"

What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.

When there are multiple duplicate names then I run into an error.

When the statement is put into a string and executed in VBA I get this error:

"Run-time error '3021': No current record."

When the statement is put into a query and run against the DB I get this error:

"At most one record can be returned by this subquery"

So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.

View 2 Replies View Related

Subqueries Performances

Jun 15, 2008

Hello,

Lets consider two tables : CUSTOMERS and ORDERS.

I would like to know which of the following query is the fastest:

select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1)

select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1) and C.Name like 'A%'

I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.

Thank you in advance for any advices,

regards,

mathmax

View 2 Replies View Related

Subqueries Performances

Jun 16, 2008

Hello,

I would like to know which of the following query is the fastest:

select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1 and O.Customers_Id = C.Id)


select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1 and O.Customers_Id = C.Id) and C.Name like 'A%'

Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?

Thank you in advance for any advices,

regards,

mathmax

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved