Selecting From Table On Cond In A Another Table

Apr 16, 2008

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.

Thanks

View 2 Replies

Selecting ONLY Records From One Table Having ALL Data Contained In Other Table (GROUP BY?)

Jul 20, 2005

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

View 2 Replies View Related

Selecting SUM From One Table As Field In Results Of Rows From Another Table.

Nov 20, 2007

Hi,
These aren't my tables, but lets dumb it down for my sake!

I have 2 tables:

CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount

There are several records in TransactionInfo for each record in CompanyInfo.

I want to:

SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)

How can I do this? Can I?

View 4 Replies View Related

Selecting From A Table, When I Only Have The Table Name As A String Format

Apr 7, 2006

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??

View 1 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

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.

View 9 Replies View Related

Selecting 2 F/k's From Same Table?

Sep 13, 2005

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.

RudeDog

View 2 Replies View Related

Selecting From One Table Inserting Into Another

Feb 18, 2007

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

View 1 Replies View Related

Selecting From One Table, Inserting Into Another

Jul 3, 2007

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

View 2 Replies View Related

Selecting FieldName From The Table

Sep 23, 2007

How can i just select filed name from the name without data. What query should we write foe that.

View 2 Replies View Related

Selecting One Row And Inserting It Into Another Table One By One

Feb 14, 2008

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

View 3 Replies View Related

Randomly Selecting From A Table.

May 3, 2004

lets say that this select statement will give me 100 records..

"Select UserID from Users where location = 'FL'"

from these 100 records, I want to randomly get 5 records. whats the best way to go about doing this

View 1 Replies View Related

Selecting Records In One Table But Not In Another 2

Dec 13, 2004

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))

Thanks for the help in advance.

View 1 Replies View Related

Selecting Only Those Records Which Are In Another Table

May 25, 2005

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.

View 2 Replies View Related

Selecting Into A Generated Table Name. Can You Even Do This ?

Aug 22, 2001

Hello,

We're trying to do the following...

Declare @Name Varchar(15)

Select @Name = 'TableName'

Select @Name = @Name + Rand(SomeSeedValue)

Select *
Into @Name
From Someothertable

This just gives us syntax errors, anyone else had any success with this ?

View 1 Replies View Related

Create Table Selecting From Another

Mar 5, 2001

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

View 1 Replies View Related

Selecting Last 100 Rows From A Table.

Jan 7, 2004

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)

View 6 Replies View Related

Selecting Last 'n' Records From Table.

Nov 17, 2005

I was given this query but could not build it successfully.
can anybody help me out.

Consider the following scenario:

we have a table with single field of type Varchar(100).
we have few hundred records in it.
we want to retrieve last 'n' records from table.

i know this is bad idea but we need to have some solution to work it out.
i'm breaking my head but to no use.

Please help

View 3 Replies View Related

Selecting From A Dynamic Table Name

Sep 25, 2015

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).

View 6 Replies View Related

Selecting From A Linked Table

Jun 20, 2008

Hi

I've created a linked table connection to an external data source within sql server 2005.

The link looks OK, I can see all the tables etc, but I want to create a view on another database to some of the tables on the linked database.

How do I perform something as simple as a select statement as I can't seem to be able to select anything.

Is there something that i need to do first in order to be able to select fields from the linked table?

Thanks

View 3 Replies View Related

Selecting Variable As Table

Jul 5, 2007

Good Day guys, sorry i'm just new in SQL Server.

My problem is like this:

I want to pass Table Object to a declared variable and make View.

Usually we make like this:

"Select * from Table1"

I want like this:

"Select * from @Table1"

I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.

Please help me guys, i really appriciated everything.

Thanks,

Ero-Sennin26

View 5 Replies View Related

Selecting Rows From One Table That Are Not In The Other

Apr 20, 2006

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

View 3 Replies View Related

Selecting Primary Key Value Into Another Table

Apr 11, 2008

I have table A with Primarykey column, AId, Identity field.

I have table B with foriegn key column , AId,

I have same number of rows in both tables (over million), but in Table B, column AId is null at present, as it was added later.

Now I need to select all AId values and update them in existing rows.

Any idea, how would my T-SQL look like???

Many Thanks,

View 9 Replies View Related

Dynamically Selecting Table In DTS

Sep 1, 2006

Hello,

I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.

Thanks

MV

View 2 Replies View Related

Selecting Exactly 1 Row But More Columns From A Related Table..

Jan 4, 2008

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...

View 6 Replies View Related

Selecting Multiple Values From One Table

Apr 29, 2008

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>

View 2 Replies View Related

SELECTing Random Record From TABLE?

Apr 24, 2001

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.

View 2 Replies View Related

Selecting Table Names In A SQL Statement

Jun 12, 2001

Hey all,

I trying to figure out if you can get a list of table names from a particular database by writing a single SQL statement.

E.G. In Oracle, from SQLPlus - you can say:

Select table_name from user_tables;

Is there anything like that SQL that I could run from Query Analyser to get a list of table names from a particular SQL Server database?

Thanks in advance,
Tommy

View 3 Replies View Related

Selecting Rows By There Position In The Table

Jan 25, 2001

Hy!

I'm trying to write a query which returns only some rows in my table...

For example :

I want all the rows included between 30 and 50 from table XYZ
(30 and 50 are not keys but really the numbers of records)

Is there someone out there who has a hint for me???

Thank you for your help and sorry for my english!

RadiFluide

View 1 Replies View Related

Selecting Bottom Rows From A Table

Dec 15, 1999

I need to select the last 6 rows from a table... I was doing it like this:

select * from table where id > (select max(id)-6 as id from table)

But if one of the records is deleted it will only show 5.... Is there any easy way to make it show the bottom 6 records?

much thanks sql gods...

View 2 Replies View Related

Insert Record Selecting Then To A First Table

May 19, 2004

Hi all,

I Have the following situation:

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?

Thanx all

View 8 Replies View Related

Selecting The 3rd Highests Item From A Table?

Feb 5, 2004

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.

View 3 Replies View Related

Selecting From Table And Adding Ur Own Values

May 19, 2008

Hey all

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 want the file to look like:

1, 31809, C:LCImportDataImages1.jpg, 1
2, 31809, C:LCImportDataImages2.jpg, 1
5, 31809, C:LCImportDataImages5.jpg, 1
etc...

I would really appreciate it if someone could point me to the right direction.

I am trying to work on it..will put up a solution if I find one myself.

Many thanks,

Rupa

View 12 Replies View Related

Selecting Groups Of Rows In A Table?

Feb 6, 2014

Table 1:
AB C
114.7
214.7
314.7

Table 2:
ADE
11a1.2
11b1.8
22a2.1
22b2.3
33a0.6

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:

Table 3:
ADE
11b1.8
22b2.3
33a0.6

Total field E = value in field C = 4.7

View 2 Replies View Related