Return The Results Of A Select Query In A Column Of Another Select Query.
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure
declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin)
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader(); can we have something likeHashtable = cmd.ExecuteReader();
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB Â Â Â Â 123 colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
Hello, In the query analyser, I execute a select ' restore database ' + name + ' from disk = " D:sql7dumpackup ' + name + '' + Name + ' _du_21.bak " with move ' ' + name + ' _Données " to " e:sqlbasesasesdata ' + name ' _données.mdf ", move ' ' + name + ' _Journal " to " d:sqllogs ' + name + ' _journal.ldf ", replace From sysdatabases
And some lines are truncated in results ?. how to avoid that.
I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST"> <select name="ManagerID" id="manager" style="width:400px" class="form_textbox"> <? $db_connect= mysql_connect($db_host, $db_username, $db_password); mysql_select_db($db_name, $db_connect) || die(mysql_error()); $sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
I have posted this on previous forums, but the below code is a VB.net CLR stored proc that will accepts two parameters, a SELECT.... FOR XML query and a file path, and saves the resulting xml result into a file specified by the parameter. After compiling the VB code into a .dll than the T-SQL code to import the assembly, create the CLR proc and test it is included underneath. Hope some will find this useful
Code Snippet 'Requires the Trial or Release version of Visual Studio .NET 2005 Professional (or greater). Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml
Partial Public Class outputxml <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub outputxml(ByVal XmlData As SqlXml, ByVal Filename As SqlString) 'Create the variables to hold the values that are supplied by the parameters 'input by the stored procedure Dim xmlDoc As New XmlDocument() Dim output As SqlPipe = SqlContext.Pipe() Try 'Load the result set into the XmlDoc Variable and then save the results in the 'path provided by the stored procedure. The values are provided by the 'input parameters of the stored procedure xmlDoc.LoadXml(XmlData.Value) xmlDoc.Save(Filename.Value) Catch ex As Exception 'If an error occurs catch the message and pipe it back to SQL output.Send(ex.Message.ToString) End Try End Sub End Class
T-SQL CODE BEGINS HERE
/*Alter the database to set trustworthy on in order
to allow the ability to set the assembly to external
I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:
SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla FROM tableX.... WHERE call_status=open.....
I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.
At the moment the SELECT query above gives me the following results:
Call Ref sla
10001 In SLA 10002 Breach 10002 In SLA 10003 In SLA 10004 In SLA 10005 Breach 10005 In SLA ... ...
What it should look like is this....
Call Ref sla
10001 In SLA 10002 Breach 10003 In SLA 10004 In SLA 10005 Breach ... ...
Please let me know if anyone has the answers or any clues to this! thanks.
Hello.Lets say that there is a table with N rows. now, i want to display the table's data on a web page. one way is to select the whole table and add each row's data to the webpage (something like SELECT * FROM TABLE1).going this way will create a huge page. i want to speared the results over multiple pages - excatly as this forum spread each forum messages over multiple pages. for this purpose, i need to query Y rows each time, for example, if my table has 20 rows, and say that i want that each page will display 5 rows, then i need to query 5 rows each time. the first 5 rows for the first page, the next 5 rows for the second page and so on... is there any way to achieve it using an SQL query? the simplest way is to select the whole table and manaually filter the results. but this way will become slow as the table grows with data... and i dont want to select rows which i wont display anyway. any suggestions? Thanks.
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
I have  created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
 ALTER PROCEDURE [dbo].[RPT_Cost_copy] SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM INTO T1 FROM (SELECT a.meu, a.mep2, SUM(a.mest) as excst         FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''   Â
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable. INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in -- descending order
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!
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"." Here is my Query, sp_configure 'show advanced options',1 reconfigure with override go sp_configure 'Ad Hoc Distributed Queries',1 reconfigure with override go reconfigure SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
i have one table called Healthmanagement in that i have column HealthTitles in that there r so many rows..few of them r here:
HealthTitles --------------------------------- Lung Health Lung Health: General Lung Health: Asperic Detailed Modified Diseases Diseases: in one generation Attacks Attacks: Health Related
i want in ouput all rows but which r with ':' i want after that part only and if the rows r without ':' then remain as it is:
so i want
HealthTitles --------------------------------- Lung Health General Asperic Detailed Modified Diseases in one generation Attacks Health Related
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS] GO /****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT ee.EmployeeID, EmployeeName = LastName + ', ' + FirstName FROM EmployeeInvData ee --UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
Hi, all. I want to Select query with incremental column on the fly.
For example Use pubs GO select * from jobs where job_desc like '%e%' Order by max_lvl returns job_idjob_descmin_lvlmax_lvl 1New Hire - Job not specified1010 12Editor25100 13Sales Representative25100 ...
I want to add here Rank Column numbering in order select RankOnTheFly, * from jobs where job_desc like '%e%' Order by max_lvl Then result will be.. Rankjob_idjob_descmin_lvlmax_lvl 11New Hire - Job not specified1010 212Editor25100 313Sales Representative25100 ..
I can get the result using cursor and looping throught and inserting or Using Identity function. But, I saw before there is just one simple Select query doing that.
Suppose I have added a xyz bit column in mytable. From now onwards new values inserted in mytable will have 0 or 1 in xyz column but the values that were previously stored will have NULL value in xyz column.
Now I want to write a query in which I will not give xyz parameter always like it can be null or value.
select * from mytable where class='something' and xyz is null
or
select * from mytable where class='something' and xyz = 1
const_cd const_name count inner query 122 a 205 4 123 b 205 4 124 c 235 2 125 d 191 3 126 e 226 1 127 f 159 6 128 g 165 11 129 h 175 9 130 i 225 2 131 j 213
I am using SQL2005 and ASP.NET 2.0 I have one column in database called DateTime and it is defined like type datetime.It is formated like: day.month.year hour:minutes:seconds My question is: I want to get date from Column DateTime in format day.month.year in SELECT query, not in stored procedure. thanks
I¡¦ve got a table with the following as well as other info:
User ID DirectoryTypeID (int) Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
I am using SQL select query to select MIN from column data but I have 12 columns and want to select MIN of all these 6 columns in one SQL select statement.
I have written as SELECT MIN(temp_sv,humidity_sv,SH1,SH2,SH3,SH4) from production but its not working... How to do this....
