Sql Query To Return Multiple Records

Nov 9, 2006

Hi,

I'm trying to retrieve some records from an SQL database.

I've a table named CustomerOrder with three fields - custID , productname and quantity

No keys in the table. it's row based approach. every custID can have multiple entries for different products.

Example:

CustID ProductName Quantity
1 Product1 2

1 Product2 3

2 XXX 1

2 Product1 2

1 Product3 4

I would like to write a query that gives the result as follows :

CustID ProductName Quantity
1 Product3 4

2 Product1 2

Meaning that, query has to retrieve only one record per custID based on highest quantity.

select custId,Productname,quantity from customerorder where quantity = (select max(quantity) from customerorder)

the above query returns only one record. (ofcourse..)

Kindly help me to get the desired.

Thank You.

View 5 Replies

Return Missing Records Over Multiple Tables. Query Challenge!

Mar 6, 2008

I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.

I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.

I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.

Here is an example:

create table TBL1(PID int, info1 varchar(10) )

Create table TBL2(TID int,PID int)

Create table TBL3(XID int,PID int)

insert into TBL1

select '1','Someone' union all

select '2','Will ' union all

select '4','Have' union all

select '7','An' union all

select '8','Answer' union all

select '9','ForMe'

insert into TBL2

select '1','1' union all

select '2','1' union all

select '3','8' union all

select '4','2' union all

select '5','3' union all

select '6','3' union all

select '7','5' union all

select '8','9'

insert into TBL3

select '1','10' union all

select '2','10' union all

select '3','8' union all

select '4','6' union all

select '5','7' union all

select '6','3' union all

select '7','5' union all

select '8','9'

I need to find the PID and the table it is missing from. So the results should look like:

PID
MISSING FROM

1
TBL3

2
TBL3

3
TBL1

4
TBL2

4
TBL3

5
TBL1

6
TBL1

6
TBL2

7
TBL2

10
TBL1

10
TBL2

Thanks all.

View 5 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SP Return Multiple Records For A Single Record

May 3, 2007

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4

that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View 8 Replies View Related

Return Single Records By Joining Multiple Tables

Jun 4, 2008

I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12

[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0

[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico

[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy

[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].

The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.

View 4 Replies View Related

Transact SQL :: Stored Procedure To Return Multiple Records

Oct 21, 2015

I am looking out for sample stored procedures returning multiple records

Example: GetOrderDetailsByOrderId

The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.

View 4 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -

SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -

<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>

View 2 Replies View Related

? Query To Return All Records In Last Hour

Apr 17, 2008

Hi

I am looking to write a query that returns all records Inserted in the last hour.

The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?

I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.

Many thanks

View 16 Replies View Related

Sql Query To Return Records That Are Only 45 Days

Dec 1, 2007

The topic pretty much sums up my question.

What would the sql query be to return only all the records in a table that are 45 days old from today.

Thank you for any help in this matter

Al

View 5 Replies View Related

Transact SQL :: How To Return Number Of Records In Query As If TOP Was Not Used

Jul 21, 2015

What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100 and therefore the TSQL would look like

SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc

Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21' ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.

View 6 Replies View Related

Conditional Return Of A Value From A Set Of Records As A Field Value In A Query

Dec 20, 2007

Hi,

I need to implement some thing like this.

I have a query like

SELECT table1.col1
,��n/a�� _response
FROM table1
INNER JOIN table2

This is the query that get the report data for my report. Now I need to replace the second column with an actual response like ��accepted�� and ��rejected��. And these values should be a result of evaluation of this form

Statusarray[] = ResponseStoredProcedure(table1.col1)

If(Statusarray.count < 1)
Set _response = ��accepted��
Else
Get Statusarray[1]
Compare this to Statusarray[0]
Set _response = some result based on comparision.

The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.

How can this be done in sql(using SQL Server 2005)

PLZZZ HELP!!!!!!!!!

Thanks,
Hari

View 2 Replies View Related

Query Doesn't Return Any Records Unless All Joins Have Matches

Jan 1, 2008

Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.

Note: The query has to run in Access 2000 and I will be coding it in VB.

SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]

FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]

GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]

HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));

View 2 Replies View Related

How To Write A Select Query To Return Records In A Many-to-Many Relationship - Junction Table???

Oct 26, 2006

Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!

View 6 Replies View Related

How To Make The SSMSE To Return Whole Records Without Any Close Query Form And Re-create Query Form Operation?

Dec 25, 2007

Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:

Select * from Table1

It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.

Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?

Thanks a lot!

And Merry X'max!!!

View 4 Replies View Related

Query To Return Multiple Date Rows

Feb 14, 2007

HI there,

Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:

163610737464753422005-12-30 00:00:00.000
163610737464753592006-03-10 00:00:00.000

This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?

Thanks!!!!!

S

View 3 Replies View Related

How To Check Multiple Records With One Query

Sep 3, 2007

i have a table which is having size and pieces information and say it is having following 4 records.

Size, pieces, sizeID, sizecombID (SizeCombination Id)

S, 1, 1, 1

M, 3, 2, 1

L, 3, 3, 1

XL, 1, 4, 1

Now suppose user enters all these 4 records which i will pass as xml string to my stored procedure.

Is there any way to check existense of all these 4 records in this table from the xml data which i pass by using one SQL statement ? Or what is the right way of checking it, Is it through using cursor ??

Finally after checking it should retrun SizeCombID for a existing record, and if record is not existent then it should add these 4 records in table and return the new SizeCombID.

Can somebody pls help on this..

View 7 Replies View Related

Help With Getting Distinct Records From Multiple Columns In SQL Query

Nov 3, 2005

I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/10/2005 12:00:00 | 23489 | 10/15/2006 15:13:23 |Desired Result: ACCT NUM | DATE OF SERVICE |________________________________ 43490 | 10/01/2006 08:15:23 | 35999 | 10/10/2005 12:00:00 | 35999 | 10/24/2005 12:45:30 | 23489 | 10/15/2006 15:13:23 |Here is the query I'm working with... just can't figure out how to join or limit the results to ONLY unique matches in Acct Number AND DateOfService. "SELECT tblCH.ProcedureKey AS CPT, tblPC.Description, DATEDIFF(d, tblPat.BirthDate, " & _ " { fn NOW() }) / 365 AS Age, tblPat.LastName, tblPat.FirstName, tblPat.BirthDate," & _ " CAST(tblCH.AccountKey AS varchar) + '.' + CAST(tblCH.DependentKey AS varchar) AS Account, tblCH.DateOfService " & _ " FROM dbo.Procedure_Code___Servcode_dat tblPC INNER JOIN " & _ " dbo.Charge_History___Prohist_dat tblCH ON tblPC.ProcedureKey = tblCH.ProcedureKey RIGHT OUTER JOIN " & _ " dbo.Patient_Info___Patfile_dat tblPat ON tblCH.AccountKey = (tblPat.AccountKey AND tblCH.DependentKey) = tblPat.DependentKey "Any suggestions from y'all SQL gurus? I have to have this report ready for production by tomorrow morning and this is the last fix I need to make =Thank you =)

View 6 Replies View Related

Query On Unique Records With Multiple Criteria

Oct 19, 2013

I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.

SCENARIO :

I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B

FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method

paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.

Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.

Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-

table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL

FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE
PTFF.TRANSDATE BETWEEN 130501 AND 130831
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))

However, the script above returns :-

1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID

Samples of the result:-

IDSTATUSTRANS_IDPAYCHNLTRANSDATEEFFDATETRANSCODE
51881712CC13082920130920T522
9361164CC13081620140813T522
78531153CC13082020130814T522
8949151CC13081220130801T522
8949251B13081220130801T522
19081455CC13051620131129T522
19082455A13051620131129T522
19081409CC11101920111129T522
19082409A11101920111129T522
19081404CC11092920111129T522
19082404B11092920111129T522

View 7 Replies View Related

T-SQL (SS2K8) :: Most Updated Records From Multiple Join Query

Mar 28, 2014

i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).

View 5 Replies View Related

Query To Return Records Where One Table Is Void Of Linked Data In Another Table

Feb 13, 2008

I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.

Thanks in advance,

Mark

View 5 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

Integration Services :: Insert Multiple Columns As Multiple Records In Table Using SSIS?

Aug 10, 2015

Here is my requirement, How to handle using SSIS.

My flatfile will have multiple columns like :

ID key1 key2 key3 key 4

I have SP which accept 3 parameters ID, Key, Date

NOTE: Key is the coulm name from the Excel. So my sp call look like

sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date

View 7 Replies View Related

Select Top Needs To Return 2 Records Only

Jan 10, 2013

I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.

id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231

I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query

select sum(series), date from table group by date order by sum(series) desc

This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.

View 1 Replies View Related

Return Duplicate Records

Feb 27, 2007

Hello experts,I'm trying the run the following query with specific intentions.I would like the query to return 5 results; i.e., 4 distinct and oneduplicate. I am only getting, however, 4 distinct records. I wouldlike the results from the '007' id to spit out twice.I'm not using 'distinct,' and I've tried 'all.' I realize that Icould put my 5 employee id's in a table and do a left or right join; Iwould like to avoid that, however. Any thoughts?SelectEmployee_last_name,Employee_first_name

Quote:

View 3 Replies View Related

Return Records Between Dates

Feb 15, 2007

Hi,

I need some help with this please.

I have a database table which contains customer orders. I am trying to code my SQL select statement to:

1) Only return records where the record orderdate is within the last 30 days

2) Between two dates, selected from the datepicker control.

With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.

There must be a more efficient way?

With regards to issue 2) ?

View 1 Replies View Related

Selecting Multiple Records By Multiple Filters...

Aug 13, 2006

Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew

View 2 Replies View Related

Selecting Multiple Records From Multiple Tables

Nov 4, 2004

i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.

ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.

View 1 Replies View Related

Delete Multiple Records From Multiple Tables

Jan 20, 2006

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

View 1 Replies View Related

How Can I Get My Sproc To Return Records With 0 As ClientID

Nov 21, 2006

Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?
If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).
If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.
ALTER PROCEDURE dbo.CMAdmin
@SID int
AS SELECT A.CompanyName, C.FirstName, C.LastName, C.ClientID,Convert(varchar(10), T.ActionDate, 10) AS [Action Date], T.Priority, T.Status, T.Subject, T.Note, T.CompletionDate, 10) AS Completed, T.DateEntered AS Entered, T.EnteredBy AS [Entered By],
CASE WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +' '+ C.[LastName] ELSE A.[CompanyName] END AS DRName
FROM tblClients C LEFT OUTER JOIN tblClientAddresses A ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T ON C.ClientID = T.ClientID
WHERE C.ClientID = Isnull(@SID,C.ClientID)

View 1 Replies View Related

Cant Work Out This SQL To Return 4 Random Records

Feb 10, 2007

I've got a product table in SQL 2000 that contains say these rows:
ProductID intProductName varcharIsSpecial bit
I want to always return 4 random specials from a query and can do this fine by using:
SELECT TOP 4 ProductID,ProductNameFROM Products WHERE IsSpecial = 1ORDER BY NEWID()
This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.
What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?
Is this possible?
Thanks

View 2 Replies View Related

How Can I Return The 5 Records From A Database Table Using Ado.net

Apr 24, 2007

I have an incident reporting management application. People are supposed to report incidents by this application and every time some one reports an incident, they also select thier employee#(reqiured field). so how can write an sql statement that returns only the Top 5 incident reporters i.e going by employee number. Iam thinking of applying a COUNT function on the incident_id and grouping by Employee# but then how do i make sure that only the top 5 incident reporters are returned.

View 1 Replies View Related

Set A Range Of Records For SqlDataSource To Return?

Mar 1, 2008

Is there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?

View 10 Replies View Related

Return A Set Of Records From A Stored Procedure

Nov 14, 2000

What is the preferred method of returning a set of records from a stored procedure? Could you please provide a short example?

Thanks ahead of time!

G.

View 3 Replies View Related