I have table HITS with columns ID and hitDate and table DATESS with columns startDate and endDate.
I can have alot of entries in the HITS table with hitDate between the times of a startDate and endDate entry in the DATESS table.
What I am trying to do is to select all the ID's of the entries where hitDate is between startDate and endDate. (In the end I would use min and max to get the firt and last hit between those times)
I have tried:
SELECT ID from hits,Datess WHERE hitdate between startDate and endDate
but it does not give the required results. I think the problem is the tables are not related in any way. Is there another way to do this.
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
is there some way to allow this to execute, using a function or something?:
select * from 'cusomer'
you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
This is, I'm hoping, a simple thing that I just don't know how to do. But I've searched my books and read a lot of threads but so far, no luck in what I'm trying to do. Here's the basic layout of 2 tables from my d/b (MSDE):
tblCity cityID (p/k) stateID (f/k to tblState.stateID) cityName (varchar 35)
tblStudent studID (p/k) cityID (f/k to tblCity.cityID) hometownID (f/k to tblCity.cityID) studFName (varchar 25) studLName (varchar 35) ...
I *hope* that what I tried to do there is allowed. Basically what I'm trying to do is to tie the student to both his current City (by foreign key cityID) and his hometown city (also by foreign key cityID).
So, if New York is (127, 33, 'New York') and Birmingham is (1050, 1, 'Birmingham') (in tblCity), then in the student table (tblStudent), John Smith would be Quote: 12150, 127, 1050, 'John', 'Smith', ... How would I format my SQL statement to get both cityNames? I thought this would work
Code:
SELECT s.studID, c.cityID, c.hometownID, s.studLName + ', ' + s.studFName AS studNameName, c.cityName, c.cityName AS swapconNum FROM tblStudent AS s INNER JOIN tblCity AS c ON (s.cityID = c.cityID) WHERE s.studID = 12150
Can I do this? If so, what am I doing wrong? Sorry, I'm kinda sorta new to SQL.
hi, i am sure this is very simple, but its just got me stuck! i have a table which i use for inserting products, now its not very exciting to see the cat_id so i wanted to put a dropdown box on there which would show the cat_description from tbl_cat and then put the tbl_cat.cat_id into tbl_products.cat_id any help would be greatful Jez
hi there, i have a page where on my button click i need to select address, pcode from tbl_person where person_id = querystring and update or insert those values into tbl_del where order_ID = querystring 2 below is the code i am using, although im stuck on the sql stringSqlConnection objConnAddy = new SqlConnection(sConnectionStringCustInfo);using (objConnAddy) { objConnOID.Open(); // insert values into delivery table, second line updates price + VAT string sqlAddy = "?????"; SqlCommand objCmdAddy = new SqlCommand(sqlAddy, objConnAddy); objCmd1.ExecuteNonQuery(); i had a look on the net and here but i cant seem to find exactly what i want, any help would be great! Cheers Jez
i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table. after that the process starts again like inserting "Emp_Id"=1 in "Concerned_Department" and so on. plz note that i want to insert "Emp_Id" one by one not all the fields together. can u plz tell me the query for that wh i ve to write in SQL Thanks and happy valentine day to all of u regards Ahmed Bilal Jan
Hi, my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
SELECT DateOfStats FROM table1 WHERE (DateOfStats NOT IN (SELECT dateofstats FROM table2)) and (DateOfStats NOT IN (SELECT dateofstats FROM table3))
i need to select records from table "A" if only the "PK" of "A" exists in table "B". I need to return a resultset not just a single record. The problem is table "B" is not a table in database instead a user supplied table which can be a datatable in memory.
Hi! I need to create a table and populate it with other table's contents. I know this can be done DTS, I wonder if there are means to make it throught a single TSQL command. Thanks, Fabio
I have a temporary table that contain 1000 rows. I want to insert records from 900 to 1000 into another temporary table. Is ther any query that will do this in SQL server. With cursors I can do that but I need one shot query. Please give me some suggestions.
I using this query but it is not returning any rows. select * into #t2 from #t1 t1 where not exists( select top 900 * from #t1 t2)
I've got an update statement with a subquery (I'll post the code further down) that I need to either make dynamic or do something else to make sure it does what I want.
The query is as follows:- UPDATE dbo.tbl_Process_List_Control_Table SET LastUpdateDateTime = ( SELECT ISNULL(MAX([LatestRowUpdateDateTime]), @LastUpdateDateTime) FROM [wtbl_Process_List_Patient] ) WHERE ProcessList = @ProcessName This is a called proc with the following parameters:- @LastUpdateDateTime is the date of the last record to be loaded @ProcessName is the name of the process that was started/finished.
I need to make [wtbl_Process_List_Patient] dynamic so it looks at a different table based on a passed parameter.I've tried making the whole thing dynamic but it states I need to declare @LastUpdateDateTime which I can't see how to do as it's already passed to the proc (as is the process name).
hi!I have two tables with same structure. I need to run a select query thatwill return only the rowsfrom one table that are not in the other.In MySQL it would beselect * from table1 MINUS select * from table2.but in MsSql I can not find the apropriate way to do this.Any Help?Zvonko
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
I have a sql select query that I'm pulling from a "Years" table to link to 3 columns in an Items table.ZCValuesYear table has two colums: YearID and YearYearID Year1 20042 20053 20064 20075 2008...I want to bind the "Year" value to the three colums in the ZCItem table: ItemUseFirstYearID ItemUseLastYearID ItemYearIDThe query below will pull all the "ID's" for each of the colums, but how would I make it pull the "Year" value (instead of record 4, it would pull 2007 instead)?<asp:SqlDataSource ID="sqlItemSelect" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>" SelectCommand="SELECT ZCPartVault.PartVaultID, ZCPartVault.PartVaultItemID, ZCValuesYear.Year, ZCItem.ItemName, ZCItem.ItemUseFirstYearID, ZCItem.ItemUseLastYearID FROM ZCPartVault FULL OUTER JOIN ZCItem ON ZCPartVault.PartVaultItemID = ZCItem.ItemID FULL OUTER JOIN ZCValuesYear ON ZCItem.ItemUseLastYearID = ZCValuesYear.YearID AND ZCItem.ItemUseFirstYearID = ZCValuesYear.YearID AND ZCItem.ItemYearID = ZCValuesYear.YearID" > </asp:SqlDataSource>
Hello. I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number. So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc... If any one have any ideas please respond. Thanks in advance.
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL, titre VARCHAR(50) NOT NULL, annee INTEGER NOT NULL, idMES INTEGER, genre VARCHAR(20) NOT NULL, resume TEXT, codePays VARCHAR(4), CONSTRAINT PKFilm PRIMARY KEY (idFilm), FOREIGN KEY (idMES) REFERENCES Artiste, FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable; How can I do it in MS SQL or Transact-sql?
I want to figure out if a student has not been to class in the last 3 scheduled days. The days do not have to be consecutive.
I have 2 tables. One table with schedules and one with attended time.
What I really need is to the find the 3rd highest MAX() from the schedule table for each student. I was wondering if someone has any idea how I could find these record?
Example scheduled records student1 2/4/2004 7 hours scheduled student2 2/4/2004 8 hours scheduled student1 2/3/2004 8 hours scheduled student1 2/2/2004 6 hours scheduled <---Need this one student2 2/2/2004 8 hours scheduled student2 1/30/2004 4 hours scheduled <---Need this one ...thousands more...
It has been a real brain teaser for me. If anyone has even an inelegant solution, I would love to see it.
This is probably the wrong category to post my question in. Apologies if it is.
I have got a Data Flow Task that has an OLE DB Source and a Flat file destination.
Is there a way to do the following:
SELECT id,'31809','C:LCImportDataImages' & id & '.jpg', '1' FROM table1
This obviously doesn't work but I want to insert the id from the table, followed by the value '31809', followed by 'C:LCImportDataImagesid.jpg', followed by '1'
So, the id in 'C:LCImportDataImagesid.jpg' is the same as the id from the table.
I would like to filter the second table taking one row per ID of Field A (first table) and selecting the rows whose sum of E is equal to the value in field C; in this example the resulting table should be:
