I am building an invoicing database. I have no problems searching for
due dates and generating the invoice header. The problem is generating
the invoice detail.
My customers may have more than one item that needs to go into the
invoice detail table.
For example:
customer #123 has 2 items that need to be placed into the detail
table.
Rate 1 email account
Rate 2 hosting account
I have to get both of these records into the detail table.
When using the conventional method, I get something alongthe lines of
" insert failed. more than one record was returned"
-------
INSERT INTO detail
SELECT (SELECT max([id])FROM iheader),CustomerRates.custid,
rates.Price, rates.name
FROM CustomerRates INNER JOIN Rates ON CustomerRates.Rateid = rates.ID
WHERE NextBill > GETDATE()
-------
I have even considered a cursor to loop through the records but I cant
make it run properly. I am not crazy about the performance of cursors
anyway.
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
Hi Can anyone help with inserting Multiple records in to db using a select command or dropdownbox or listbox If I have a dropdownbox with 10 records in using a select query DRopdownbox data = select username, memberId, age from Memberslist where age = 30 I also have a textbox on the form called messageDetail Insert username, age and messagedetails into Messages for each item in dropdownbox
one company can have multiple shareholders and directors records.
i create a search query where users might search by company name, secretary name , shareholder name or directors name. My select query is like below:
Code:
SELECT dsf.dsf_id, dsf.company_name, dsf.incorporation_date, dsf.secretary_name, s.shareholders_name, d.directors_name FROM tbl_dsf dsf LEFT OUTER JOIN tbl_directors d on dsf.dsf_id = d.dsf_id LEFT OUTER JOIN tbl_shareholders s on dsf.dsf_id = s.dsf_id [WHERE CONDITION]
The result for above query would be like:
Code:
abc | 1/2/1999 | william | marry | donna abc | 1/2/1999 | william | jenna | donna abc | 1/2/1999 | william | jolly | donna abc | 1/2/1999 | william | marry | dolly abc | 1/2/1999 | william | jenna | dolly abc | 1/2/1999 | william | jolly | dolly
Is it possible to achive result as below:
Code:
abc | 1/2/1999 | william | marry,jenna,jolly | donna,dolly
Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
insert into table1 (colname) values (value1) can only insert one record into the table. How to insert multiple records as value1, value2, value3... into a table? We can of course use the above repeatedly, but if I don't know how many records (which is a variable), and I want to write a code which just take value1, value2, value3 .... from the clipboard, to paste as a input. How to insert those multiple records into table without split it. Thanks
Hi.. I really need a help. Is there any way to insert multiple records into a table in one go?
I have a table named Fruit. It contains FruitId,OwnerId,Colour
The list of colour is got from another table name FruitColour. FruitColour Consists of 2 column, FruitName and Colour.
Is it possible to insert multiple records into Fruit table with one query if only the colour is changed.
Sample case I have an Apple. Fruit id=2 OwnerId=2 Colour -- > Select Colour From FruitColour where FruitName='Apple' (Will return multiple records)
I tried to insert using this query: Insert into Fruit(FruitId,OwnerId,Colour) Values (2,2,Select Colour from FruitColour where FruitName='Apple').
Gives me this error Subqueries are not allowed in this context. Only scalar expressions are allowed.
I need to do this because actually I am inserting multiple fruit at one time, and each have multiple colour. If I need to insert the colour one by one for each fruit it will takes a very long time.
Any suggestion are welcomed. Thank you in advanced.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
hello, I am new to Slq 2000 Database,Now I create an asp.net application with sql 2000, in my database I have two 2 table lets' say "OrderHead" and "OrderDetail",they look like this: OrderHead orderdetail ---order no ----orderno ---issuedate ----itemname ---supplier ----desccription ---amount -----price ----Qty Now I created a user-defined Collection class to storage order detail data in memory and bind to a datagrid control. I can transfer Collection data to xml file ,my problem as below : There have multiple records data in my xml file,and I want to send the xml file as argument to a store procedure in sql 2000
anyone can give me some advise or some sample code ?
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
I'm trying to update a checkbox from "False" to "True" within a single table for multiple records. I can update a single record using the script below. However, I'm having trouble applying additional Id's to the string.
(Works) - Update Name_Demo set KEY_CONTACT = 'true' where ID = 225249
(doesn't work) - Update Name_Demo set KEY_CONTACT = 'true' where ID = '225249, 210014, 216543'
It says query executes successfully but returned no rows.
I have struggled and can't seem to get the SQL correct to get the records I want. I have 2 tables containing information from 2 separate independant sensor arrays which collect data about trains in a rail network. I need to get the most current records about the trains at any given time.
For any given moment in time, I want to query the database and get the last known position of each train and it's status. So I want a result table like this: given time = 10:07:20 TrainId | PosTime | X | Y | StatusTime | SensorId | Delay | OffDuty 109 | 5/10/2007 10:07:15 | 62389 | 25002 | 5/10/2007 10:06:51 | 75 | -5 | N 210 | 5/10/2007 10:07:15 | 65489 | 25432 | 5/10/2007 10:04:54 | 85 | -10 | Y 857 | 5/10/2007 10:07:15 | 62889 | 25983 | 5/10/2007 10:05:21 | 231 | +1 | N
OR given time=10:03:30 1 | 109 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:34 | 72 | -3 | N 2 | 210 | 5/10/2007 10:03:30 | someX | someY | null | null | null | null (Since train 210 it hasn't tripped a status sensor yet, we don't know it's status at that time) 3 | 857 | 5/10/2007 10:03:30 | someX | someY | 5/10/2007 10:02:48 | 199 | +2 | N
Any help to achieve these results would be MOST appreciated, Thanks.
How do I select data from another database name (in the same SQL SERVER LOCATION) and its child table? SELECT user_id FROM [10.10.20.3].CC_host.usr_table Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By user_id
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I used Express Manager ( XM ) to try the select statements. That is how I isolated the problem. Even using a "Select * from timecards where employee = 'test' " returns the inserted test records. I found that if I use a WHERE or ORDER BY clause in the SELECT statement to .fill the .net dataset, all records are returned.
I am familiar with DB2 but I am a newbie at VB.NET and MSSQL
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE TABLE [dbo].[User] ( [UserId] [bigint] NOT NULL ,[country] [int] NULL -- User’s home country ,[name] [nvarchar](15) NULL -- User’s displayable name (‘John’, ‘Bill’) ,[subscriptionTier] [int] NULL ) -- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute) John 2010-10-16 00:21:56 1500 Dave 2010-10-16 00:30:50 3200 Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link, 'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts JOIN tblProduct p ON ts.ProductID = p.ProductID JOIN tblTool t ON ts.ToolID = t.ToolID JOIN tblOperation o ON ts.OperationID = o.OperationID WHERE ts.ToolID=66 ') oq LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
