Get Only Single Row Results Per Id?

May 26, 2007

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.

For example i have an Articles table, and a PicturesForArticles table.

The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).

And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)

Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.

So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.

Is there any way to do this, to only return on row of results for each aid?

Thanks

View 4 Replies

Transact SQL :: Group Results Into Single String

Jun 4, 2015

I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.

Current Query:

select
t.Name 'Task',
d.FirstName + d.LastName 'User'
from [dbo].[Tasks_TemplateAssignTo] a join
Task_Template t on a.template_id = t.ID join
Doctor d on d.id = a.provider_id

Results from query above:

TaskUser
Call CustomerJohn Smith
Call CustomerBetty White
Call CustomerTammy Johnson
Order suppliesGreg Bullard
Order suppliesJosephine Gonzalez

Expected Results:

TaskUser
Call CustomerJohn Smith, Betty White, Tammy Johnson
Order SuppliesGreg Bullard, Jospehine Gonzalez

View 4 Replies View Related

Integration Services :: Two Results From Single Task

Jul 22, 2015

I have a table with email addresses and CC_Flag.

Email     | CC_Flag
xxxx           0
yyyy           1
zzzz           1

Using Task SQL, I am trying to pass these email addresses to two separate variables - To_field, Cc_field on basis of the CC_Flag..Is it possible to fill two variables from a single SQl Task,.

View 4 Replies View Related

Assistance With Insertering Query Results Into A Single Table Row.

Feb 14, 2008

I have a table that was created as follows
create table call_summary (

row_date smalldatetime,
[700] int,
[730] int,
[800] int,
[900] int)

I have a query that returns the following
date interval (int) calls (int)
2008-01-07 00:00:00 700 0
2008-01-07 00:00:00 730 0
2008-01-07 00:00:00 800 0
2008-01-07 00:00:00 830 9
2008-01-07 00:00:00 900 8

I am looking for a way to get my table mentioned above to look like this
row_date [700] [730] [800] [830] [900]
2008-1-7 0 0 0 9 8

does anyone have any slick ideas on how I can accomplish this task?

View 3 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.

After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

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!

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date

Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000

Should Appear

Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008

Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View 4 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***

View 2 Replies View Related

Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks

View 3 Replies View Related

PASTE SQL RESULTS INTO EXCEL - Funny Results

Jan 30, 2008

Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.

View 6 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Single Value Of SqlDataSource

Jul 5, 2006

I have set up a sqlDataSource that returns one value.
I would like to assign the value to a string.
How would I acomplish this,

thanks

View 5 Replies View Related

Return Single Value

Dec 3, 2006

Hello, I created a stored procedure which selects a value according to ContentId. I know that will be only one value returned or none. So if a record is found I want to return the string contained in ContentHtml. Else I want to return the string "NotFound" Could somebody help me out with this? Here is my present stored procedure: -- Specifies the SQL-92 equals compliant behavior SET ANSI_NULLS ON GO -- Specifies the SQL-92 quotation mark rules SET QUOTED_IDENTIFIER ON GO -- Alter procedure ALTER PROCEDURE [dbo].[by27_Content_GetContent] -- Define the procedure parameters @ContentName NVARCHAR(100), @ContentCulture NVARCHAR(5) AS -- Prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Declare and define ContentId DECLARE @ContentId UNIQUEIDENTIFIER; SELECT @ContentId = ContentId FROM dbo.by27_Content WHERE ContentName = @ContentName -- Check if ContentId is Not Null IF @ContentId IS NOT NULL BEGIN -- Select localized content from by27_ContentLocalized SELECT dbo.by27_ContentLocalized.ContentHtml FROM dbo.by27_Content INNER JOIN dbo.by27_ContentLocalized ON dbo.by27_Content.ContentId = dbo.by27_ContentLocalized.ContentId WHERE (dbo.by27_ContentLocalized.ContentCulture = @ContentCulture AND dbo.by27_Content.ContentName = @ContentName); END -- Create procedure GO Thanks, Miguel

View 1 Replies View Related

Getting A Single Value From SqlDataSource

Mar 5, 2007

Hi,
I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1.
DDL1 Selected data field to display is ProductNameDDL1 Selected data field to value is ProductCost
I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1.
Happy Coding

View 1 Replies View Related

Need A Single Query

Jun 14, 2007

Hi,
I have received a text file in the following format:
MthYear                  Customer                     Quantity           Price           Total
Apr2003                 Allan                              100                 5                500
---------                  Austin                             25                  2                  50
---------                 George                           1500              1               1500
----------                Jessy                                200               2                 400
Apr2004                Jerry                             600                 3                 1800
---------                 Stella                            250                 2                   500
June2005            XXXX                               50                  5                    250

I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie
The MthYear field for Austin, George,Jessy should be updated with Apr2003,
The MthYear field for Stella should be updated with Apr2004
iiily, for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.

View 3 Replies View Related

Single Or Multiple Sp ???

Nov 1, 2005

greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............

View 3 Replies View Related

Bcp Single CPU Vs Multiple CPU

Oct 20, 2001

I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them.

Is there something obvious that a single CPU box would run this much faster?

View 5 Replies View Related

Selecting A Single Row

Nov 16, 2001

I have a query that returns a certain amount of results. How do I select the last row??? I know I can do the top row by doing (top 1). Basically I need the complete opposite. Please help...

I looked around and couldn't find anything on it.

thanks in advance

View 2 Replies View Related

BCP A Single Table

Sep 28, 1999

I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why.

The table only had a few rows of data and only had a few small dependancy tables.

The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long.

Here is the BCP command line that I ran :-

bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n

Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one.

Failing that, is there any way of scripting the transfer of a table from one server to another.

Many thanks.

Dave

View 2 Replies View Related

Single Query

Jan 7, 2007

Hello

I have a sql query that shows the top N numbers of records and also wanted to show the total numbers of this records.

Code:

.
.
.
rs_1.Open "SELECT top 5 msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3

rs_2.Open "SELECT msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3 �

Top number to show: <%=rs_1.recordcount%>
Total number of records in database: <%=rs_2.recordcount%>
.
.
.

As you can see, it is only on the same database that I am querying. How do I make a single sql query out of this?

View 3 Replies View Related

Single Query

Jul 6, 2004

i hav a table of the form

table EMP
(
EMPNAME varchar(50)
)

and the data in the table is

record #1 abc
record #2 abc
record #3 abc
record #4 abc
record #5 abc
record #6 abc

can i use a single query so as to retain a single record in the table and remove the rest ?

View 14 Replies View Related

Need A Single Sql Statement

Feb 23, 2005

How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

1st query
Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

2nd query
Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2

View 3 Replies View Related

Is It Possible In Single SQL Statement

May 31, 2006

Does anyone know how should I write the sql for getting the following result?

Original Table like below.
-------------------------------
[WorkDay][AgentCode]
06/12/01 3
06/12/02 2
06/12/02 3
06/12/03 2
06/12/03 3
-------------------------------

Curernt SQL:

When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

Example,
SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
-------------------------------
[WorkDay][AgentCode]
06/12/02 2
06/12/03 2
-------------------------------

I would like to know the agent is in the specified date.
The expected result like below.
-------------------------------
[WorkDay][AgentCode]
06/12/01 NULL
06/12/02 2
06/12/03 2
-------------------------------

Please help its urgent

View 9 Replies View Related

Single Quote Before A Value

Apr 23, 2014

I ran a large query and exported it to xls. In Excel, I noticed a value of '2.00E+01'. I formatted the Excel cell and then got a value of '20'. I searched for the record in SQL Server 2008, ya I know, ancient. The value is '2E1' which excel is seeing as scientific notation.

Can I use a case statement that when an 'E' is present to add a single prefix quote like '2E1? Or perhaps do it for the whole column? Or put put double quotes around the whole field?

View 2 Replies View Related

Get All Data Into A Single Row?

May 6, 2015

I'm trying to get all this data into a single row, but instead I'm getting results like a staircase with multiple rows. What concept am I missing here?

View 5 Replies View Related

Single User

Jul 4, 2006

How to remove the label "Single User" in the database?

View 2 Replies View Related

What Is Single Usermode?

Mar 13, 2007

Dear Experts,
while i'm practicing DBCC commands, it is saying the database should be in single user mode. how can i change my database with ALTER Database command?

and what are other modes of a database?

thank you very much

Vinod

View 2 Replies View Related

Single Row Output

Apr 24, 2007

Hi
i hav a query as

select skill_id from o_skills where employee_id=78

Output of the above query is:

skill_id
-----------
5
13
3

But i want output as

skill_id
--------
5,13,3

how can i get the above result
Thanks in Advance

Malathi Rao

View 4 Replies View Related

Single Statment

Mar 13, 2008

iam having two statement for updating one table.
just i need to club into one statment.can any one tell how this can be achieved.

first statement:
===================
--Updating Customer code
update stg
set stg.customer_code=dtl.cgd_customer_code
from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_group_dtl dtl(nolock)
on dtl.cgd_cust_group_code=stg.customer_grp

second statment:
===================
--updating customer name
update stg
set stg.customer_name=dtl.clo_cust_name
from Subsidiary_Ageing_tb stg(nolock) inner join fin_ods..cust_lo_info dtl(nolock)
on dtl.clo_cust_code=stg.customer_code

View 1 Replies View Related