I have a table in my database that records lab tests for patients where I work. There's colums for the PatientID, TestID, and TestDate. Each patient can have multiple tests.
I need to pull the top 4 tests, by TestDate, for each patient in the table. I've been trying to do it with the Select Top(4) clause but no luck so far. Any help would be appreciated.
Thanks.
Hello all
quick question-- Im looking for the most effiecient way to extract data daily from a table with some 9.5 mill records and growing. These are transaction records and ideally I would like to bring over the last days transactions and add them to my existing table. I cannot use the transaction date as sometime we have to operate in an "Offline" mode where the records are brought over sometime later. This could be days are unfortunetaly a week or more. there are some 30 fields in the transaction table so is there a more efficient way to do this simply creating a concatenated key?? Would it be more effiecient to drop and recreate the table daily? that sounds extreme so wanted to get a few ideas.
Thanks in Advance
km
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.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00'
AND EXPIRATION < '2007-02-28 00:00:00'
AND OPT_TYPE = 'CALL'
Related DB fields.
STOCK_SYMBOL
STRIKE
UND_LAST
OPT_TYPE
EXPIRATION
Any help would be appreciated.
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables
ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
Hello,
I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today?
How does time affect this?
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
View 7 Replies View RelatedHey,
What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work.
Thanks!
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
View 5 Replies View RelatedI have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
View 4 Replies View RelatedI have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
View 4 Replies View RelatedHi guys
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table
where type = 'Extra Mailbox'
and active = 1
and value not in
(select login from view)
order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
Any ideas?
Thanks in advance for any responses.
Ging
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
View 5 Replies View RelatedHello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
how to write a query to find the no of tables present in that database.
Any suggestions.
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
Hi there,
Is there a way where I can run a query or a store procedure where i can retrieve all the contraints attached to the tables in the database?
thanks
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
Guys ...
I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.
There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.
Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.
Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.
Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005
do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?
Hello all,I'm trying to run a query to make a report. My database is a incidentreporting database. I'm tryng to make a monthy report for incidents.The field I need to query in the date field which is a nvarchar in theform of 01/01/04 and 01/01/2004. I ran a query that looks like this:SELECT incident, doccur, IDFROM dbo.IncidentWHERE (doccur between '01/01/2004' and '01/31/2004')I get some results that look like this:Unsecured doors01/19/0492INTOXICATION 01/17/0477Bill Door entry door 01/28/03130Hit & Run01/21/04105Customer complaint01/02/0370Customer complaint01/02/0491PRINTER MALFUNCTION01/22/04111Customer complaint01/30/042322Trash Smoldering01/15/0451LOST01/02/0380BROKEN GLASS PANEL01/13/0442B.I.A. Assist01/04/03189GAS LEAK01/06/048UNCHANGED CASH BOX01/11/0440Intoxication01/17/0469Intoxication01/02/0471Intoxication01/17/0472Employee accident01/17/0473GREASE FIRE01/18/0474Verbal Dispute01/17/0475PANHANDLING01/17/0476Near Miss/Water backup01/18/0478Unsecured Arcade Door01/19/0493Intoxication01/18/0479Intoxication01/02/0481SUSPECT/WANTED01/18/0482Intoxication01/18/0483Property Damage01/20/0384Unsecured Bingo Snack Bar01/18/0485PANHANDLING01/18/0486Employee accident01/19/0487Unauthorize of proper exit01/19/0488Safety Hazard01/19/0489Key control violation01/02/0390Cracked keno ball01/23/04116Employee accident01/19/0494delay in drop01/27/2003128test01/01/20053763As you can see, the querey will give me the month and day I ask for,butnot the right year. Some to the data has 2 digit years and some have 4digits. How do I design the query to give me the year I ask for.Any assistance will be greatly appreciated
View 7 Replies View RelatedHi,I'm having two databases in the name of n1,n2 having same designI want to query both the databases by giving the database name in queryDatabasesN1N2Table(both database contains same table)DetailTable detailsNo -intName -varchar(10)Now i want to check the detail table in both n1 and n2 using the selectqueryFor that I have written query it is not workingselect * from N1.DetailSelect * from N2.DetailThe above query is not working in query analyser,I'm getting error as"Invalid object name"In query analyser the Northwind database is currently selected, anywayI have given the database name it has to query from N1 database. How touse a select query with database name?Mani
View 1 Replies View RelatedI realize that this may not be the correct ng for this question, andif so, I'd appreciate a pointer to the correct group.I am not including DDL/DML for this question, because I do not believeit is relevant.What I have is a SQL query, which when executed in Query Analyzertakes about 10-12 seconds. From the time I issue the query, I see aninitial lag of about 1 seconds after which the grid starts gettingpopulated, and then runs its course.The UI responsiveness is impressive. Almost immediately, well, within1 second at most, I see data appear from the server into my queryanalyzer grid.I'd like to get the same responsiveness if possible on my clientapplication written in C# using ADO.NET.I use the SqlDataReader class off the SqlCommand class to make thesame query (identical), and yet it takes close to 9 or 10 secondsbefore even the first row is retrieved by the application. I am notsaying that it takes 9 or 10 second for my DataGrid to start beingpopulated; it is simply that the first Read operation of theSqlDataReader after I connect (which is instantaneous), that takes 9to 10 seconds. Once the data retrieval starts, the grid populates inshort order.Are there any pointer by which I can get just a few rows fast enoughto give the user the impression that the query is executing. In otherwords, what techniques can I use to get the same sort ofresponsiveness as in the SQL query analyzer?In case it is relevant, there are some 250,000 rows returned by thequery.Thanks for all helpful responses in advance.
View 8 Replies View Relatedhello ,
I have 2 tables that are linked and i am querying sencond table from 1st table here is how my query looks. It works fine when i enter the parameter value from 1st table , it gives me the assoaciated values in 2nd table, But i also want it to display without filtering the second table when the parameter value is NULL ie when there are no associated values in 1st table. I tried putting "IS NULL" but it doest work. Can any one please help me out with this
Thx
SELECT DISTINCT dbo.group_primary.site_group_primary
FROM dbo.group_primary INNER JOIN
dbo.group_conversion ON dbo.group_primary.id = dbo.group_conversion.primary_group_id INNER JOIN
dbo.group_secondary ON dbo.group_conversion.secondary_group_id = dbo.group_secondary.id
WHERE (dbo.group_secondary.site_group_secondary = @secondary) OR
(dbo.group_secondary.site_group_secondary IS NULL)
If I do the following
SELECT *
FROM MyTable
WHERE DateCreated = '27/07/2007'
It fail's, what am I doing wrong?
Thanks
I was wondering if someone could help, I am new to T-SQL and programming in general and have done a bit of work, that works but I would like some help to improve.
I have two views, I have a stored procedure selecting data from one and dumping to a local physical table then another select statement using the second view and dumping the results to the same table as the first, I then have clients querying that physical table.
What I would like to do is have the stored procedure do all the work thereby negating the need for the two views and the physical table.
Here is what I have:
View1:
SELECT BLCUSA AS Company, BLCUSB AS Account, BLDELN AS DeliverTo, blcnam AS [Account Name], BLCTS1 * 100 AS Monday, BLCTS2 * 100 AS Tuesday,
BLCTS3 * 100 AS Wednesday, BLCTS4 * 100 AS Thursday, BLCTS5 * 100 AS Friday
FROM LIVEAS400.S65C422B.WRFDTA.PARDADR AS PARDADR_1
WHERE (BLCUSA = '2')
View2:
SELECT abCUSA AS Company, abCUSB AS Account, 0 AS DeliverTo, abcnam AS [Account Name], abCTS1 * 100 AS Monday, abCTS2 * 100 AS Tuesday,
abCTS3 * 100 AS Wednesday, abCTS4 * 100 AS Thursday, abCTS5 * 100 AS Friday
FROM LIVEAS400.S65C422B.WRFDTA.PARNADR AS PARNADR_1
WHERE (abCUSA = '2')
Stored Procedure
USE [WINDRUSHDAL]
GO
/****** Object: StoredProcedure [dbo].[spgiffordstelesales] Script Date: 02/28/2008 11:31:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spgiffordstelesales]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF OBJECT_ID ('Phone_Times_Copy') IS NOT NULL
DROP TABLE Phone_Times_Copy
Select *
INTO Phone_Times_Copy
From head_office_phone_times
Insert into phone_times_copy select * from deliver_to_phone_times
Select *
From phone_times_copy
END
Hi. I'm new to SQL on a large scale and I'm having some issues with a DB I'm trying to make a query to. Below are the 4 tables and at the end the query I'm trying to get functioning correctly. Basically I'm trying to create a query of classes for a specific student based on ones that are not in the student's major. So for instance if the student had 2 classes, one part of a computer science major and one part of some other major and the student had computer science as his major it would query only the non computer science major. It worked fine until I added multiple majors into the query that is where I read about using TOP 1 to get it work. Yet it still doesn't. I tried using INNER JOIN, LEFT JOIN, and RIGHT JOIN to no avail.
I hope someone can help!
Code Snippet
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Classes] Script Date: 03/26/2008 18:34:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Classes](
[StudClassID] [int] IDENTITY(0,1) NOT NULL,
[StudentID] [int] NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditID] [int] NULL,
[Days] [nchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time] [nchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Semester] [nchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Completed] [tinyint] NULL,
CONSTRAINT [PK_Student_Classes] PRIMARY KEY CLUSTERED
(
[StudClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_ClassID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_CreditID] FOREIGN KEY([CreditID])
REFERENCES [dbo].[Credits] ([CreditID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_CreditID]
GO
ALTER TABLE [dbo].[Student_Classes] WITH CHECK ADD CONSTRAINT [FK_Student_Classes_StudentsID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Classes] CHECK CONSTRAINT [FK_Student_Classes_StudentsID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Classes] Script Date: 03/26/2008 18:34:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Classes](
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LongName] [nchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED
(
[ClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorRequiredClasses] Script Date: 03/26/2008 18:35:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MajorRequiredClasses](
[MajorClassID] [int] IDENTITY(0,1) NOT NULL,
[MajorDisciplineID] [int] NULL,
[ClassID] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorRequiredClasses] PRIMARY KEY CLUSTERED
(
[MajorClassID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_ClassID] FOREIGN KEY([ClassID])
REFERENCES [dbo].[Classes] ([ClassID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_ClassID]
GO
ALTER TABLE [dbo].[MajorRequiredClasses] WITH CHECK ADD CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[MajorRequiredClasses] CHECK CONSTRAINT [FK_MajorRequiredClasses_MajorDisciplineID]
USE [C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL.1MSSQLDATACOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Student_Majors] Script Date: 03/26/2008 18:35:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Majors](
[MajorDisciplineID] [int] NOT NULL,
[StudentID] [int] NOT NULL,
CONSTRAINT [PK_Student_Majors] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC,
[StudentID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_MajorDisciplineID] FOREIGN KEY([MajorDisciplineID])
REFERENCES [dbo].[MajorDiscipline] ([MajorDisciplineID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_MajorDisciplineID]
GO
ALTER TABLE [dbo].[Student_Majors] WITH CHECK ADD CONSTRAINT [FK_Student_Majors_StudentID] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Students] ([StudentID])
GO
ALTER TABLE [dbo].[Student_Majors] CHECK CONSTRAINT [FK_Student_Majors_StudentID]
SELECT
Student_Classes.StudClassID
,Student_Classes.Completed
,RTRIM(Student_Classes.ClassID) AS 'Class ID'
,RTRIM(Classes.LongName) AS 'Name'
,Classes.Description
,RTRIM(Student_Classes.Days) AS 'Days'
,RTRIM(Student_Classes.Time) AS 'Time'
,RTRIM(Student_Classes.Notes) AS 'Notes'
,RTRIM(Student_Classes.Semester) AS 'Semester'
FROM Student_Classes
,Classes
WHERE Student_Classes.StudentID = '0'
AND
Classes.ClassID = Student_Classes.ClassID
AND
(SELECT TOP 1 MajorRequiredClasses.ClassID FROM MajorRequiredClasses WHERE MajorRequiredClasses.MajorDisciplineID != (SELECT TOP 1 Student_Majors.MajorDisciplineID FROM Student_Majors WHERE Student_Classes.StudentID = '0')) = Student_Classes.ClassID
Hi,
I got a query problem, which might not be able to be done, please point out my problem and helps, thanks. Here I got four table (I'm thinking on a forum logic)
Forum
=======================================
fId int(7) , auto increment
fTitle varchar
fSection varchar
Thread
=======================================
tId int(7), auto increment
tTitle varchar
fId int(7), should be link to Forum.fId
Post
=======================================
pId int(7), auto increment
pContent Text , by the way, what is there TEXT datatype in MSSQL?
pDate DateTime
uId int(7), link to user profile
tId int(7), link to thread table
UserProfile
=======================================
uId int(7), auto increment
uLoginName varchar
bla bla bla
When I query the result, I want to query the total thread count and post count for corresponding forum, Is there possible to query out a table something looks like this?
Result
=================================================================
fId fTitle fSection threadCount postCount pId uId pDate
001 A Admin 5 100 001 005 2007-06-06
002 B Admin 0 0 NULL NULL NULL
003 C Others 123 12345 142 001 2007-06-02
This is what I want to do:
At the first page, the page will query above result (assume it can), and it seperate each forum according to the fSection.
I want retrieve all the Forum records, then I want to count total Thread that corresponding to each fId, as well as total of the Post. I want get a pDate which sort by Desc for each corresponding fId where then it shows the uId that posting it.
Maybe I'm not make myself clear enough, perhaps should someone point me to solution or tells me how forums query run at their first page. Thanks.
I have a table where I need to pull all the component parts for a given part number. In the fictious data below, the information I need to be able to pull back is all the components for the part number 4444ZZZ-01.
Table Name = Product Structure
PARPRT_02 COMPRT_02
4444ZZZ-01 102441
4444ZZZ-01 102442
4444ZZZ-01 801277
801277 101483
801277 D801277
I know how I can pull the general information
Code SnippetSelect PARPRT_02, COMPPRT_02
FROM [Product Structure]
WHERE PARPRT_02='4444ZZZ-01'
But what I am having trouble with is stating IF the COMPRT_02 is also listed in PARPRT_02 then show this information also. I have tried to research all kinds of joins(inner, self, right, left), unions, derived tables....but most of the explanations I have found only go so far and I have found nothing that really nails it.
Thanks
(this table basically is a Build of materials where a given model has multiple parts and any given part might have
sub parts listed under them) example 4444ZZZ-01 is a computer with a part 801277 that is a wiring assembly. The part 801277 has two parts associated with it - 101483 - the wiring assembly itself and D801277 a document file.
Hi!
Does anyone know how to query for the job duration of all jobs in a server?
Thanks!
Hi,
I'd like to get information about tables defined in the SqlCe20 database. So I tried to use the following which doesn't work:
string sql = "SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"cmd = new SqlCeCommand(sql, connection);cmd.Parameters.Add(newSqlCeDataParameter("A", DbTypes.NVarChar);cmd.Prepare();//later in codecmd.Parameters[0].Value = tableName;reader = cmd.ExecuteReader();while (reader.Read())....
Here the reader.Read() returns always false indepent of the table name.
In contrast doing the following without parameter works as expected:
reader = cmd.ExecuteReader(string.Format("SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName));
while (reader.Read())
Because I have to execute this statemen often I have to use prepared statement with parameters.
How can I get it working?
Miroslaw
Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..
View 1 Replies View RelatedHey there!
I'm not sure how to explain this but here goes...
I'm a bit stuck, a new project that's come about "requires" me to query multiple databases as if they were tables.
1 Oracle database which stores information on our staff (this database is a part of some MIS software and can't be changed in any way)
1 SQL Server 2005 database which stores information on staff sickness
Basically say theres 1 table in each database
Oracle DB
People
Person_Code
Forename
Surname
SQL DB
Sickness
Person_Code
Daydate
Lets say the query I want to perform is to select all the records from sickness where person code is 22334 and also get their name from the other db, so the output may look like
22334 Dann Rees 01/01/2007
22334 Dann Rees 03/01/2007
22334 Dann Rees 10/02/2007
Now I realise I can write a quick function to pull the information but this is just a basic example. Effectivley what I "need" is to be able to query sickness while sub querying people to get the names, or some kind of pass through query?
Please remember this is just a very simple example and the "actual" queries will be far more complicated, for instance finding all the employees of a certain department who is male and was sick in January. All the data for that example is stored in people (oracle) except for the dates which is stores in sick (SQL 2005). Now these are easy enough if they were tables in 1 database....but their not, their tables in 2 databases, and theres nothing I can do to change that :(
All help appreciated as this is becoming very urgent.
Many thanks
Dann
(I couldnt post this in the General data access forum for some reason)