Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only.
If I do this:
SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4)
I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4
NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned.
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC , vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: } Line 1445: try { Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery(); Line 1447: return returnValue; Line 1448: }
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
I have done queries before where you have something like this: Select name From people Where uid in (Select UID from employees where salary > 500000) as an example. I have to use stored procedures, I can't connect directly... I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records. Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set CheckedOut = 1 Where UID in (@UIDList)
I have tried a few different things, none works. Any ideas how you might accomplish this with stored procedures? The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that. Thanks.
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example: selectcmd = "Select * from mytable where myfile =" ??????? cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values: A B C D E
I would like to build a query like that: selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
I need to create a stored procedure that takes a list of product numbers(541, 456, CE6050,...) and selects only the customers that have all product numbers in the list. I have a product table that has the following schema:
So a customer can have more than one product, but I need a select statement that takes the product numbers and selects them against the product table and returns only the customerNumbers that have the entire list of product numbers. I have tried doing a join on the product list and productNumber, then join with the customer table on customerNumber, but that gets any entires found in the list not the entries that have all the product numbers. Thanks in advance for any help.
Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid
I have a table that has a list of skills, ie "HP One", "HP Two"
I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below.
DECLARE @SkillSet NVarChar (200) SET @SkillSet = '-HP One-,-HP Two-' SET @SkillSet = Replace(@SkillSet, '-', '''') SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet)
The following sp does not return any results, but when i set the last line to ..
SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two')
It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two'
Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is.
Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
Hi, I would like to create a Stored Procedure for obtain a list of insert using the information of a select queryI have to do X insert for days, using my start selectConstant : start dayx insert for each day if in my select I obtain 550 records and x = 100I've to do 100 insert for day with information of the selectplease note that in the final day I have to insert only 50 records and not 100 (500 and 100 are not mutiples) Thanks
I have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id'; It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag -------------------------------- 1 2012-12-12 rowA 1 2012-12-12 rowB
The following returns all base tables within the database of type "varchar":
Code: SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM mydb.information_schema.columns WHERE TABLE_SCHEMA = 'master' AND TABLE_CATALOG = 'mydb' AND DATA_TYPE IN('varchar')" AND TABLE_NAME IN( SELECT TABLE_NAME FROM mydb.information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'mydb' AND TABLE_SCHEMA = 'master')
What I then want to do is... For each of these results:
Code: select [COLUMN_NAME] from [TABLE_SCHEMA].[TABLE_NAME] WHERE ID = 'test'
Is it possible to do this in one SQL command? Or do I manually have to do it for each in the list from my first query?
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g.
SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title HAVING COUNT(SubString(DiscNo, 0, 3)) > 1
SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist ORDER BY Artist, Title, DiscNo
Drop TABLE #TempArtistTitle GO
See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title.
SC8151-10 - Garth Brooks - Friends In Low Places SC8125-04 - Garth Brooks - Friends In Low Places LG5003-07 - Garth Brooks - Friends In Low Places
I want to create a column alias to represent the comparison of twocolumns (ie a boolean result of True or False). A simple example is:Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit[color=blue]>From VehicleFinance[/color]but I get an error 'Incorrect syntax near >'Books online states that the select_list can contain column_name orexpressionAn expression is a column name, constant, function, any combination ofcolumn names, constants and functions connected by an operator[color=blue]> is a binary operator[/color]So why do I get this error.Incidentally, if I use an arithmetic operator such as +, there is noproblem.
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
I have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN [DatabaseName$Sales Shipment Header] ON [DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code] UNION ALL SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
SELECT FriendName from Friends where RegionId = 23 I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey). How would I do that?
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
