Can We Combine These 3 Statements Into One Single Query
SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View Complete Forum Thread with Replies
Related Forum Messages:
Combine Data In Single Row From Single Table
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 Replies !
How To Combine 3 SQL Statements Into 1?
I have the following 3 SQL statements that need to be combined, ifpossible. The output of one feeds the input of the next. I need to viewall of the defined output fields (the output needs to be used in aCrystal Report).The SQL Follows:/* Input is ISBN (vendor_part_number) */QUERY_1 - returns 1 recordselect p.product_id, v.name, m.description, p.author, p.title,p.revision_number, p.copyright_edition, p.vendor_part_number,p.conforming_flag,m.code, mp.unit_price_product, mm.quota_pricefrom T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,T_MULTILIST_PRODUCT mp, T_MULTILIST m,T_MULTILIST_MEMBERSHIP mm where/* p.vendor_part_number == input */p.vendor_part_number = '0153364475' and p.medium_type ='TEXTBOOK' andp.product_id = pv.product_id and pv.type = 'CONTRACT' andpv.vendor_id = v.id andp.product_id = mp.product_id andm.code = mp.multilist_code and m.proclamation_year =mp.proclamation_yearand m.proclamation_seq_id = mp.proclamation_seq_id andm.code = mm.multilist_code and m.proclamation_year =mm.proclamation_yearand m.proclamation_seq_id = mm.proclamation_seq_id/* The above should return a single record */QUERY_2 - returns 2 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id =90321/* 90321 = result from above: pr.parent_product_id = p.product_id*/QUERY_3 - returns 18 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id in(90322, 90323)/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =p.product_id */Only 21 records are returned from these combined queries. I need accessto all of them even though there are 3 different resultsets, 2 of whichcontain the same fields. Is there a way to simplify this into a storedprocedure or a view that can take 1 input parameter? It needs to beused in a Crystal Report, which is limited in its handling of thesetypes of complex queries.
View Replies !
Combine These Two Sql Statements
SELECT bms_id,email_address,COUNT(*) INTO #temp FROM emp_db WHERE email_address IS NOT NULL GROUP BY bms_id,email_address ORDER BY bms_id DESC,COUNT(*) DESC SELECT bms_id COUNT(*) FROM #TEMP GROUP BY bms_id ORDER BY COUNT(*) DESC How can i put these two statements into a single sql statement. Thanks.
View Replies !
Combine Update Statements....help...
Hi guys! Is there a way to combine these update statements? Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @syain_No", cnn) Dim update_phase2 As New SqlCommand(" UPDATE TE_shounin_zangyou SET " & " phase=2, phase_states2=06,syounin2_sysd=CONVERT(VARCHAR(10),GETDATE(),101) WHERE [syain_No] = @syain_No", cnn) The same table is updated so I think it would be better to have just one update statement. But the problem is that, the first update statement retrieves values from another table, whereas the update values of the second statement is fixed. Is there a way to combine these two statements. I tried to do so but it does not update. Here's my code... Dim update_phase As New SqlCommand("UPDATE TE_shounin_zangyou SET TE_shounin_zangyou.syain_No=TE_zangyou.syain_No, TE_shounin_zangyou.date_kyou=TE_zangyou.date_kyou, TE_shounin_zangyou.time_kyou=TE_zangyou.time_kyou FROM TE_zangyou WHERE TE_zangyou.syain_No = TE_shounin_zangyou.syain_No", cnn) Please help me. Thanks. Audrey
View Replies !
Is It Possible To Combine 2 SELECT Statements??
I am not sure if this is possible, but I was wondering if I can combine 2 SELECT statements so as to aquire a percentage.. I could be overthinking this....I am fairly new to SQL writing. Here is an example of the 2 SELECT statements that I am using: Code: SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good' Code: SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' Within my output I am than taking the the data from the 1st query and dividing it by the 2nd query to get a percentage. I was hoping that I could accomplish the same action within one SQL statement. Thank you for your consideration!
View Replies !
Combine Two SQL Queries With Separate Where Statements
I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?
View Replies !
Combine Columns From Two SELECT Statements
I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers). I have two statements that look like this: SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid FROM Relationship INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber Each statement correctly aggregates the sums, but I need a record that shows me: CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire). Any help is appreciated.
View Replies !
Combine Data In Single Row
SELECT * FROM dbo.empBenefits q WHERE (StartDate IN (SELECT TOP 2 STARTDATE FROM EMPBENEFITS WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON' ORDER BY startdate ASC)) I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View Replies !
Combine Multiple Records Into Single Row
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View Replies !
Script To Combine Multiple Rows Into 1 Single Row
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View Replies !
Script To Combine Multiple Rows Into A Single Row
Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View Replies !
Combine Multiple Rows Into Single SQL Record
Hello: I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on. productID SectionID statusID 10 1 0 10 2 1 10 3 2 10 4 1 10 5 3 10 6 1 11 1 0 11 2 1 11 3 2 11 7 3 11 8 3 Need to return two rows with the respective values for each section. productID section1 section2 section3 section4 section5 section6 section7 section8 10 0 1 2 1 3 1 11 0 1 2 3 3 Any information or if you can point me in the right direction would be appreciated. Thanks
View Replies !
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
Single SQL Update Statements
What is the single SQL statement to truncate the blank space on either side of data. Ex. Table1 has Name as column. I have records filled with blank space on both side for Name field. With one query I want to correct (truncate the leading and trailing space) the data. How? SQL Server 2005 SP2. Thank you, Smith
View Replies !
How To Run Multiple Sql Statements From A Single File?
Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net? I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time. Thanks for your help in advance!
View Replies !
Multiple Select Statements In Single Stored Proc
Hi, I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too. Basically those are SELECT statements. I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report. If possible, can anyone show me the format? Thanks
View Replies !
How To Combine Two Tables (not Query)
I am new to SQL Server development, but I use the automated features in Enterprise Manager a lot. I have a table with a specific format already existing in a SQL Server 2000 database. This is generated once a day from a flat file received from an outside vendor. I am now receiving a similar flat file from another vendor which is nearly identical, but with two differences. First, the new flat file is missing two columns (not critical data). Next, there is one column that is out of order in comparison to the other flat file (aside from the 2 missing columns). I need a generic example of how to remove specific records from a table and add these new ones (from the new flat file) through the SQL Server. My intention is to have a job run at a specific time through the SQL Server. Any help is appreciated. If you know of a good tutorial or something out there, I would be more than happy to check it out. Thank you so much for your help!
View Replies !
?combine Detailed And Count In One Query?
Is it possible to combine a detailed query with its related count and sum without using any #temp tables at all? ex. select customerID, customerName, (select count(orderID) from tblOrder where orderDate > '01/01/2003' and orderStatus = 'active') as countActive, (select count(orderID) from tblOrder where orderDate > '01/01/2003' and orderStatus = 'inactive') as countInActive from rfCustomers something like that? I was heard SQL2k has some new feature like this, or a UDF may be required? Currently, I have to use #temp table to get it. thanks David
View Replies !
Combine 2 Queries In To One (joint Query)
I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing. (Query 1) SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location FROM category_products, data, models WHERE category_products.category = '36' AND category_products.product = data.model_id AND data.model_id = models.id AND data.active = 'y' $manufacturer_id=$data["manufacturer_id"]; (Query 2) SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name FROM inventory_types, vendors WHERE inventory_types.manufacturer_id = '$manufacturer_id' AND inventory_types.default_vendor_id = vendors.id
View Replies !
Combine Sql Query Result Columns?
This might be a question with an extremely easy answer.. I don't know but here I go. I want a report with lets say |A | B | C | ---------------- I can easily figure out the sql statements to find the columns A, B and C individually but how do I combine them? so lets say I have select cola as A from table1 where .... select colb as B from table2... They are not from the same table so I cannot combine them either (I cannot do select cola, colb from table1 etc.. ) How would I do this? Am I missing something?
View Replies !
Union Query Or Some Other Way To Combine Fields And Text
The following query gets all the data I need except for one new field that I need which combines multiple fields and some text. Here is the query: SELECT [Make Mods-Additions HERE].StockNumber AS ProductID, [Make Mods-Additions HERE].[Long Description], [Make Mods-Additions HERE].[Short Description], [Make Mods-Additions HERE].NEWwholeEachCost AS [Wholesale Each], [Make Mods-Additions HERE].Units, [Make Mods-Additions HERE].[Sale/CameoPrice] AS [Case Price], [Make Mods-Additions HERE].MinQty, [Make Mods-Additions HERE].Multiples, [Make Mods-Additions HERE].UPC, [Make Mods-Additions HERE].MSRP, [Make Mods-Additions HERE].[Availability Date], [Make Mods-Additions HERE].[Item Description (Detailed)] AS [Full Item Description] FROM [Make Mods-Additions HERE] WHERE ((([Make Mods-Additions HERE].Active)="YES")); I need one more field named 'Rep Order Description' that concatenates the following: [Short Description], "-$", [Wholesale Each], " ea, MSRP $', [MSRP] It is important that the [Wholesale Each] and [MSRP] values are in 0.00 format (they are currency) Example of output: Short Description-$0.00 ea, MSRP $0.00
View Replies !
Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?
I'm trying not to use a temp table, but i may have to do so.. I'm using sql2005 for this case. i have a derived table that makes the following results: ID Status Name 2 1 "A" 2 2 "B" I want to get the following: ID Name1 Name2 2 "A" "B" but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
View Replies !
Combine 2 Rows From Derived Table Into 1 Row W/o Repeating Query?
I'm trying not to use a temp table, but i may have to do so.. i have a derived table that makes the following results: ID Status Name 2 1 "A" 2 2 "B" I want to get the following: ID Name1 Name2 2 "A" "B" but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
View Replies !
Using IIF Statements Inside A Query?
Hi, I'm wondering if it is possible to use IF statements in a query, for example if this was my query: SELECT Asset, Source, Val1, Val2, Val3 FROM tableA Say the sign of the Vals is always positive, but based on if the Source field is null i want to make the Vals negative. Could I do something like this: SELECT Asset, Source, (IIF Source = null, Val1*-1, Val1), (IIF Source = null, Val2*-1, Val2), (IIF Source = null, Val3*-1, Val3) FROM tableA When I try something like this it doesn't work, is there a way to do this in a query? Thanks.
View Replies !
Maximum UNION Statements In A Query
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
View Replies !
Help With Optional Parameter Query With IN Statements
I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn€™t not pass in the parameter. I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters. One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it€™s run! Not a good solution, but maybe it€™s the best I can do . . . I have tried many different approaches using different functions, etc. but I€™ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated: 1. To add the parameter to the WHERE clause and test for NULL I€™ve used the COALESCE function such as €œWHERE table.fieldname = COALESCE(@Param, table.fieldname)€?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails. 2. To handle multiple items, for example, if @Param = €˜3,7,98€™ (essentially, a csv separated list of keys) Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname)) doesn€™t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I€™m using Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™)) which works brilliantly in my WHERE statement AS LONG AS @Param ISN€™T NULL. So how do I test for NULL first and still use this approach to multi-item parameters? I€™ve tried Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,€™,€™)) and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,€™,€™))) This fails with €œSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression€? due to the multiple values in the parameter. (I can€™t understand why the line without the CASE statement works, but the CASE line doesn€™t!) Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
View Replies !
How Should I Write This Query Wiht Case Statements
Hi I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34 Case expressions may only be nested to level 10. And This is my sproc-- ================================================ -- Template generated from Template Explorer using: -- Create Scalar Function (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date, ,> -- Description:<Description, ,> -- ============================================= Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int) RETURNS datetime AS BEGIN DECLARE @Month int, @Year char(4) SELECT @Month = [Month], @Year = Cast([Year] as char(4)) FROM Period WHERE PeriodId = @PeriodId RETURN CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE CASE @Month When 5 Then '5/31/' + @Year ELSE CASE @Month When 6 Then '6/30/' + @Year ELSE CASE @Month When 7 Then '7/31/' + @Year ELSE CASE @Month When 8 Then '8/31/' + @Year ELSE CASE @Month When 9 Then '9/30/' + @Year ELSE CASE @Month When 10 Then '10/31/' + @Year ELSE CASE @Month When 11 Then '11/30/' + @Year ELSE CASE @Month When 12 Then '12/31/' + @Year ELSE null END END END END END END END END END END END END END Any help will be appreciated. Regards Karen
View Replies !
Using SQL Query Columns In Select Case Statements
I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP: Select Case tCOTSSoftware("Type") Case 1 execute an SQL Update Command Case 2 execute a different SQL Update Command End Select What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
View Replies !
Select Query Involving Many JOIN Statements.
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--. The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it. Is there a different way i should be structuring my select statement? SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email FROM #temp_pregovb, [Survey Return] INNER JOIN SelectedInvited ON [Survey Return].SelectedID = SelectedInvited.SelectedID --*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname INNER JOIN [panelist Contact] ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID WHERE [panelist contact].email=#temp_pregovb.email AND SelectedInvited.CellRef IN ( SELECT surveycell FROm [Survey Cells] WHERe SurveyRef='5')
View Replies !
Need A Single Query
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 Replies !
Single Query
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 Replies !
Can This Be Done In A Single Query?
Hello. I am new to this forum and pretty new to SQL. My background is in firmware development but due to the smallness of our company I have inherited a VB6/SQL app in which I need to fix a problem. Given the following table: C1 | C2 | C3 ------------ 01 | 03 | 05 01 | 04 | 10 02 | 03 | 05 02 | 04 | 20 I need to select all rows for each C1 value where C2 values are equal and C3 values are not equal. In this case, rows 2 and 4. There are many C1 values in the actual table I need to query, but the query will be comparing only two C1 values each execution and these two values are passed into the app by the user, but usually more than two C2 values are in the table for each C1 value. Is this possible to do with a single query? If so, I have been unable to get the syntax correct. Any insight is appreciated. John F.
View Replies !
Single Query
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 Replies !
Can A Single Query Do This?
I have the following table: id geounit parentgeounit Here's some sample data: 1 Northeast null 2 Southeast null 3 New Jersey 1 4 Florida 2 Basically I need to return the parentgeounit's name, for instance for record #4 I'd want to return 4 Florida Southeast Instead of: 4 Florida 2 Can this be accomplished?
View Replies !
Single Update Query
Table Name:-emp table structure:- Name Gender A Male B Male C FeMale D FeMale I want only one update query to update this table like this table structure:- Name Gender A FeMale B FeMale C Male D Male please help me?
View Replies !
How To Take Single Value From Sql Query For Repeater ?
So I have code to fill my repeater with a data: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("db_ConnStr").ConnectionString) Dim cmd1 As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id INNER JOIN forum_posts AS forum_posts_1 ON aspnet_Users.uID = forum_posts_1.user_id GROUP BY aspnet_Users.UserName, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date HAVING (forum_posts_1.topic_id = @topic_id) ORDER BY forum_posts_1.post_date ASC", cnn) cmd1.SelectCommand.Parameters.AddWithValue("@topic_id", Request.QueryString("ID")) Dim ds As Data.DataSet = New Data.DataSet cnn.Open() cmd1.Fill(ds, "posts") Repeater1.DataSource = ds.Tables("posts") Page.DataBind() cnn.Close() End Sub And I want to take the value from column which I bolded - IlePost. I want take this out at compare with some condition. For example:Dim label as label = Repeater1.Findcontrol("Label1")If COLUMN < 10 thenlabel.text = "Less than 10"elselabel.text = "More than 10"I hope you understand me :-)
View Replies !
I Am Really Confusing Please Help Me About A Single Query
Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11 category_child12 category_child13 category_child23 category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul
View Replies !
AND Query In Single Column
Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply
View Replies !
2 Group By In A Single Query
Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai
View Replies !
Query On A Single Word
Hi i am using Access as my database (i know its an MS SQL forum but that's the closest i could find, and maybe its more of an sql question). I am trying to select all rows that contain i.e. the word "Art" SELECT * FROM table1 WHERE column1 LIKE %Art% as you can see column1 contain some words and not just "ART" however i am getting also the following: CART , ARTI, DARTS - since all has arts. ( i can use '=' since the column consist of other words) The query should also load : Art. Art, Art! Art? and "ART" Is there an easy solution or i need to create a huge condition ?
View Replies !
I It Possible To Get This Result By Single Query ?
hello i am a beginner i it possible to get this result by single query ? i got a int column and a datetime column is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony so the end result will look like this year 2007 | current_month | tonys_group ----------------------------------------------- $ 222 $22 $2
View Replies !
Select 1-20 In Single Query.
Hello, I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table. If any one have solution, then please help me.
View Replies !
|
TRACKER
