I'm writing an ASP page for a project and it requires multiple queries.
However, I'm trying to combine multiple SELECT statements, but can't
figure out a way that actually works.
Basically, here are the SELECT statements I want to combine:
SELECT * FROM schoolrequest WHERE SLid=10
SELECT SLlastName FROM academicoffice
WHERE schoolrequest.SLsendToId=academicoffice.AOid
SELECT SLlastName FROM academicoffice
WHERE schoolrequest.SLbillToId=academicoffice.AOid
SELECT SLlastName FROM academicoffice
WHERE schoolrequest.SLrequestorId=academicoffice.AOid
SELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10
AND diaryentry.DEdeletedDate IS NULL
The academicoffice table just contains basic contact info, but needs to
be used 3 times in the query to get specific contact info for 3
different contacts(SLsendToId, SLbillToId, SLrequestorId)
The diaryentry table contains info entered in by students. The
DEkeyValue is actually the primary id of the schoolrequest table(SLid).
Hi I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute. I need to join them up similar to the following but when I do it take forever for them to execute. select * from table where column not in (select column from table2) It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run. Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database? Any help would be appreciated. Thanks, Frankie
************************************************************* <code> tags added by moderator. Please use <code> and </code> tags to bracket comments to make code readable.
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data' Dim Conn As SqlConnection Dim Rdr As SqlDataReader Dim strConn As String = AppSettings("doConnect") Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')" Conn = New SqlConnection(strConn) Dim Cmd As New SqlCommand(strSQL, Conn) Conn.Open() Cmd.ExecuteNonQuery()
'SQL Get Last ID Dim ConnGet As SqlConnection Dim RdrGet As SqlDataReader Dim strConnGet As String = AppSettings("doConnect") Dim strSQLGet As String = "select MAX(id) as maxid from USERS" ConnGet = New SqlConnection(strConn) Dim CmdGet As New SqlCommand(strSQL, Conn) ConnGet.Open() If CmdGet.ExecuteScalar() <> 0 Then RdrGet = CmdGet.ExecuteReader() While RdrGet.Read() Session("theID") = RdrGet("name") End While Else Session("theID") = 0 End If
Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.
SELECT COUNT(*) AS [NUMBER OF SERVERS] FROM DBO.OS WHERE (OSTYPE LIKE '%SER%')
The other statement is
SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS] FROM DBO.OS WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')
If any one has any idea's please reply or email me directly. I would greatly appreciate it.
Hello everyone... I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server. Any advice would be greatly appreciated.
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?
I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */ USE Database
/* these params are on the page in drop down boxes*/ DECLARE @ProductID int; DECLARE @BuildID int; DECLARE @StatusID int;
/* static params for this sample */ SET @ProductID = -1; SET @BuildID = -2 SET @StatusID = -3
/* the query that will build the datagrid. currently this runs and produces three different result sets. How do I combine these statements so they produce a single set of results? */
IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?
I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.
Thanks very much for any help!! mike123
CREATE PROCEDURE [dbo].[select_mailbox_Count] ( @userID int ) AS SET NOCOUNT ON
SELECT count(*) as totalInbox,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead, (SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied, (SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox, (SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash
FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C. If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work. Here is what the query looks like that I'm trying to run. Thanks in advance for any help.
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY (SUMMARY_PERIOD, CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU) VALUES (GETDATE(), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), )
Hi, I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries: 1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6) 2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10) 3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was: SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working: SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?
I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.
I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?
select clientnumber,count(distinct clientnumber) as cccnt, 0 as ckcnt, 0 as cacnt from dbo.purchases where orderdate >= 20120101 and orderdate <= 20121231 and payment_type = 'CC' group by clientnumber;
OUTPUT currently looks like this: 1234 2 0 0 1234 0 1 0 1234 0 0 4
Is it possible to result in this, along with a flag with the criteria above?: 1234 2 1 4 Y
Hi everyone, I am reading about the Rollback transaction but I'm not sure if it's the feature I need. My application is going to update a few tables, but my programmer said that it will be done using more than 1 transaction. For example, if I want to create a new employer with all the detailed informations, tables will be filled when the user complete the insertion of a part of data. If he decides to abort the operation, i'd like to delete all the values inserted before with the other queries. Is it possible to create a "savepoint" and roll back all transactions processed from this savepoint?
HiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
Greetings,I have what seems a simple problem....I want to save the output from the following queries in 1 file in'SQL Query Analyser', but I can only save each 'grid' separately.I have about 30 queries in total. 'Messages' tells me the number ofrows affected but I need the data.So can I save all the grids together at the same time in 1 file ?ThanksMikePRINT 'HEAT 3510'SELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,d.tpitdaydate AS Payroll_Date,c.tishactualenddate AS TS_end_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidLEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050614'and (d.tpitdaydate > '20050614'or d.tpitdaydate IS null)order by d.tpitdaydateSELECT f.persfirstname AS First_Name,f.perslastname AS Last_Name,a.rsrchqnumber AS Personal_nr,b.asgtassignmentid AS Contract_nr,ISNULL(c.tishcode,'') AS TS_Id,c.tishactualenddate AS TS_end_date,g.thisdaydate AS TSH_dateFROM resources aINNER join assignments b ON b.asgtrsrcguid = a.rsrcguidINNER join timesheets c ON c.tishasgtguid = b.asgtguidINNER join users e ON e.userguid = a.rsrcuserguidINNER join persons f ON e.userpersguid = f.persguidINNER join timesheethistory g ON g.thistishguid = c.tishguidWHERE a.rsrchqnumber = 80000147and b.asgtassignmentid = 0000001234and c.tishactualenddate > '20050619'order by g.thisdaydatePRINT 'HEAT 3213'
I am currently using enterprise manager to run multiple queries on asingle table in a DB. I refresh these queries every few minutes. Dueto the huge number of them I was looking for a better way (or should Ijust say "a way") to manage/save these queries so I can recall themeasier/faster for monitoring purposes. Suggestions?TIA.
I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
In my DB i have hundreds on queries setup for all different reporting purposes..
We have just changed they way our system handles costing and are moving from a average cost function to a standard cost function.. This in turns, means that i need to replace any reports where i use the average cost field and replace it with the standard cost field.
Is it possible, that i can do a full search and replace over all my queries, looking for a specific field name and replacing it with something else.
I am dreading the idea of opening each individual queries and checking if it exists..
If anyone knows of software or someway to write a SP to accomadate this, please let me know. I would be most grateful.
I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server. I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number. The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.: 1: Database A -DSNA_tblMaiin - Serialnumber 2: Database B -DSNB_tblMaiin - Serialnumber 3: Database C -DSNC_tblMaiin - Serialnumber
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT * INTO dupContactKeys FROM Contacts WHERE ContactKey IN ( SELECT ContactKey FROM Contacts GROUP BY ContactKey HAVING COUNT(*) > 1)
SELECT * FROM dupContactKeys WHERE ContactKey IN ( SELECT AddressLine1, Zip FROM Contacts GROUP BY AddressLine1, Zip HAVING COUNT(*) > 1) ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
As a part of DBA, I used to execute various SQL files. Most of the time, it is like a manual effort to execute the files individually.
I am looking to automate the process, like a single click to execute all the .SQL files.
The main hurdle I have is, some files needs to be executed in A1 database, some in B1 database and some other SQL files need to be executed in C1 database. In this scenario, I need to pass the DBName information to the powershell query dynamically.
My design for this requirement is, say each .SQL file need to contain a template like
@DBName = 'your Database name' @Executeon = 'When to execute'
In this case, the powershell first need to read the SQL file and finds the value for @DBName and replace it in the powershell query and execute the SQL files automatically.
Is it feasible ? Or any other alternate easier way to proceed.
Is there any way to create a chart using data fields from more that one query?
I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.
I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.
I have to run this 3 times for similar but slightly different parameters. I only change the very top peice of code slightly each time:
1. Partition by Forename, Surname, DOB, Postcode 2. Partition by Forename, DOB, Postcode 3. Forename, Surname, DOB.
As you can see very subtle change, ideally I'd like to find a way to run just one report where all of the above occur, the issue I face running separately is one person may appear on 1 or more giving duplicates.
USE HealthBI_Views;this bit below is basically grouping my output on Forename, Surname, DOB & Postcode. What the whole process is trying to achieve is to show where a patient which has all of the above fields identical but has a different patient identifier suggesting that the service has allocated to unique identifiers to the same person which will result in both records needing to be merged.
WITH cte AS (SELECT *, COUNT(HEYNo) OVER ( PARTITION BY Forename, Surname, DOB
[code]...
--- this bit below is simply showing all instances where the above criteria is met and there are more then one instances.WHERE countOfHeyNo > 1 --- The final output display all patient identifiable information from the MF_PATIENT table so that the report can be created in SSRS and run routinely by the Data Quality Team who can then investigate each occurance.
