I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255)
select e.ssn,
e.firstname,
e.lastname,
( @path = select coalesce(@path + ', ', '')
from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt
where s1.spaceID = 133225
select @path)
from employees e
where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure:
Hi.I have an insert query which inserts record that are returned from a select subquery: INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE... col1 and col2 in tbl1 combined ,are a unique index. So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1. The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
Hi. I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint). How can I solve this ?
I'm new to SQL within the past year and am having quite a bit of difficulty trying to replace NULL in a column cast as smalldatetime. What i'd like to do is return the value "EMPLOYEED" within any record containing a null value, obviously I need to CAST the column to a varchar but when I try and do this I keep getting errors. I'm trying hard to find the answer online but the only examples I get are is for money and i'm not sure i'm completely understanding how this is supposed to work. I've also tried using the ISNULL function as well - getting the same errors in this case (though i'm using ISNULL successfully in another column).
It seems to me that I should be able to write the syntax as the following:
CAST(ISNULL(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
----OR---
CAST(COALESCE(TerminationDate, 'EMPLOYEED') AS Varchar(20)) AS TerminationDate
I'm new enough that I could just be confusing myself, but I really seriously cannot find any good explanations or examples online for what I am trying to do.
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END + CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END + .... CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END FROM TableName A
hi,i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.
I am trying to apply the logic from the following resource: URL....but cannot get it to work with my logic for some reason.For example, the following query:
;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1), CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
Produces the following output:
Col1 | Col2 2014-05-08 10:55:54 | 2014/05/08
But, as soon as I try to do something else like:
;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1), CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2) SELECT CTE1.Col1,CTE2.Col2 FROM CTE1 CROSS JOIN CTE2 GO
I receive the following errors:
Msg 8155, Level 16, State 2, Line 1 No column name was specified for column 1 of 'CTE1'. Msg 8155, Level 16, State 2, Line 2 No column name was specified for column 1 of 'CTE2'.
Are there limitations when trying to use multiple CTE in a single query?
How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
Let's say I have a table of users. Let's imagine there's two fields: username (PK), password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to (1) SELECT * FROM [User] WHERE username = 'whatever' AND password='whatever' or (2) SELECT * FROM [User] WHERE username = 'whatever' and then in my code check that the record returned matched the password?
I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID
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.
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match 2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W W44x300 300 W42x200 200 (and so on) WT22x150 150 WT21x100 100
(and so on) MT12.5x12.4 12.4 MT12x10 10 (etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W FROM AISC_Shape_Table WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
I need to query out multuple rows of data from the following table and retrieve it as a single row. Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL. Here's an example of the data:UserDefinedFieldId UserDefinedRowID FieldValue1 1 date (stored as text)2 1 text3 1 text1 2 date (stored as text)2 2 text3 2 text...27 UserDefinedFieldIds for each row in the actual table I wrote a working query, but I need something more efficient for this particular application. (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.) Already tried shorter table names, which only saved me about a 1000 characters. SELECT (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 1) AS Column1, ,,, (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId Any suggestions would be appreciated. Thanks, Brad
i just can't find a way to perform this Select Query in my ASP.Net page. I just want to find out the sales for a certain period[startDate - endDate] for each Region that will be selected in the checkbox. Table Sales Fields: SalesID | RegionID | Date | Amount This is how the interface looks like.Thank You.
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g. I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount, FROM table1 GROUP BY month(date)
Total amount is the SUM(amount) for the month. Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like: userid, name, groupids 12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins: select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:
SELECT * FROM Transactions WHERE MyField LIKE (SELECT MyValues FROM SearchValues)
But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.
And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.
I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.
Sql Statement: (this statement is not allowed) Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'
Desired Result:
AccountNo RecordID Description Amount 221 20 Whew 290.00 221 21 Hi There Good Morning 728.00
Table 1
RecordID Description 20 Whew 21 Hi 21 There 21 Good Morning
I have two tables. Customers and Orders. Customers table contains customer id, and customer name columns. Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.
I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought. Can I write this sql without using a subquery.
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1 I need to write that kind of sql to return me single value a and multiple values b on each of lines, like a b ---------------------------- tom small,big,hugh But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
1) Getting this error: Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near the keyword 'in'. Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near ','. Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. 2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason... Here is my procedure.
alter procedure SP_ImportHotels_Step4 As BEGIN INSERT INTO classifieds_Photos ( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL ) SELECT classifieds_Ads.Id, [i.ID] in (Select Min(Images.ID) from Images group by HotelID), GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID) INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID; END go execute SP_ImportHotels_Step4
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20)) go create table aaa2 (id_no int, name varchar(20)) go
insert into aaa1 values (1,'rahmi') insert into aaa1 values (2,'atilganer') insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi') insert into aaa2 values (5,'atilganer') insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/ declare @id_no_var int /* and set variable to max table name's (aaa2 table in this example) id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help */
set @id_no_var = (select max(id_no) from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3 Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run select * from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp --------------- aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name, and concetanate to query, and execute with exec
table1 member_name legacy_id team_name ----------------------------------------- Bill 1234 nationals Bill 1234 nationals Tom 3456 nationals Tom 3456 orioles
I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.
I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.
My query should return
team_name count ----------------- nationals 2 - because it occurs for bill, and tom orioles 1 - because it occurs for tom
If I do something like:
select distinct(team_name), count(team_name) from table1 group by team_name
I get back:
team_name count ------------------- nationals 3 - because it occurs for bill twice, and tom once orioles 1 - because it occurs for tom once
I've tried something like:
select team_name, count(team_name) from table1 where legacy_id in ( select distinct legacy_id from table1 )
I get a syntax error. Regardless, I'm not sure this will give me what I need.
I've tried over a dozen variations of select distinct, joins, etc but with no luck.
Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.
SELECT Field1, (SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1 SELECT Field1, (SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I try execute it with SQL Server Everywhere it says "Token in error = SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
