Can I Do An Sql Query Then Use The Result Of That In Another Query...
Aug 29, 2007for instance i need to find value of storyId from one table then make sure i do not pull that record from another table. is this possible
View 6 Replies
ADVERTISEMENT
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
Nov 7, 2007I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Same Query Gives Result With Different Column Sequence When Used In Query Analyzer
Feb 25, 2012When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
Re-display Result Set Without Re-running Query In Query Analyzer?
Apr 9, 2006I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedTransact SQL :: SELECT On Column Name From Query Result Set In Same Query?
May 9, 2015I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
Saving Query Result To A File , When View Result Got TLV Error
Feb 13, 2001HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
End Result Is Main Query Results Ordered By Nested Result
May 1, 2008As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
Return Result From One Query As A Column In Other Query
Jun 3, 2004I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
Save Query Result At CSV Or TXT File From The Query
Dec 10, 2007We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
Thanks
Amit K Patel
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Jan 28, 2008Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
Getting Result Of Query Inside Another Query
May 22, 2007Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
View 4 Replies View RelatedHow To Use Result Of One Query In Another Query?
May 15, 2007Hello,
I have a Data Flow.
I have 2 databases.
I need
for example
1. Get a user name from Database 1 (Table 1)
2. Get a user name ID from Database 2 based on the user name from above. (Table 1) (it will be query using INNER JOIN)
3. Use the user name ID result in my Data Flow.
What control can I use for it and how?
Should I use Lookup? If yes - how can I use the result from the first query?
So, In the end I should have User name ID for each user name in my new table which will be in Database 2.
Thanks.
How To Get Sum() Result In Query?
Mar 15, 2008mail_delivery_master----------------------ml_id ml_from_mail_id ml_to_mail_id ml_user_id ml_subject ml_content ml_file_attached ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1 aa bb cc dd ee 2 joe@hotmail.com tt@gmail.com, JOHNYJP Forum answer somebody answer 0 1 3 joe@hotmail.com mailme@hotmail.com, JOHNYJP Forum answer somebody answer -1 0 4 joe@hotmail.com mailme@hotmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 5 joe@hotmail.com janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 6 joe@hotmail.com dff@gg.com, JOHNYJP Forum answer somebody answer 0 0 7 joe@hotmail.com tt@gmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 8 Durai@gamial.com yogesh@gamail.com durai test test 0 0 9 janani@ajsquare.net devi@ajsquare.net janu test hi 0 0 10 janani@ajsquare.net devi@ajsquare.net JANANI test Miss u -1 2 mail_attachementsml_id ml_file_name ml_file_format ml_file_size ml_file_select_from ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1 chocolate .jpg 114528 F: empchocolate.pjg 2 Tiger Caspian Blue.jpg .jpg 1114407 F:imageTiger Caspian Blue.jpg 3 Autumn.jpg .jpg 66287 F:imageAutumn.jpg 4 Ascent.jpg .jpg 63244 F:imageAscent.jpg 5 Azul.jpg .jpg 61365 F:imageAzul.jpg 6 daisy.jpg .jpg 8197 F:imagedaisy.jpg 7 Stonehenge.jpg .jpg 59600 F:imageStonehenge.jpg i want the result :ml_id , ml_from_mail_id, ml_to_mail_id from mail_delivery_master ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss
View 2 Replies View RelatedQuery Result As O/p From SP
Oct 29, 2005Hi thereI want a stored procedure that contains a Select query. I want SP that return result of query(rows)how could i do this?please help
View 2 Replies View RelatedQuery Result Help
May 17, 2005Hi,all,
I ran the two queries and I thought it would be the same, but it's different.
Can you explain to me.
Query 1: result---52 rows
select s.InsuredSurname, s.email from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')
and s.agentcode <> '130844'
Query 2: result--14 rows
select s.InsuredSurname, s.email from tis_studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email IN (Select I.Email from tis_InterMedical I)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844'
Thanks!
Betty
Using Result Of One Query In Another
Sep 29, 2004hi,
i've tried searching on this but not having much luck, i'm trying to use the result of one and use it in another. is there any way to do this?
So for example, i would use the result of this query and store it in a variable called @tmp_id (if possible)
SELECT TOP 1 ID FROM tblWines WHERE RefNo LIKE 'AB1234';
then use that variable in another query
INSERT INTO tblLogWines (WineID, Date) VALUES (@tmp_id, now());
i've simplied the context to make it a little more understandable, any help is appreciated.
goran.
How/what Should Be The Query For This Result
Jun 28, 2006I have 1 table "Progress"P_no b_no status build_date----------------------------------------------------------------25 1 First_slab 2006/4/525 1 second slab 2006/5/625 2 first slab 2006/1/225 2 third slab 2006/2/3o/p should be asPno,bno, status, max(build_date)sample o/p can be as below25 1 second slab 2006/5/625 2 third slab 2006/2/3Thanks in Advance.
View 4 Replies View RelatedQuery: How Can I Get This Result?
Jul 20, 2005Hello all,I tryed to simplify the problem as much as possible. I'll start with theDDL:----------------------------------CREATE TABLE #MyTable(NoID INT,Type CHAR,DateTransaction DATETIME)INSERT INTO #MyTable (NoID, Type, DateTransaction)SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALLSELECT 2, 'C', '2004-01-01' UNION ALLSELECT 3, 'B', '2004-01-01' UNION ALLSELECT 4, 'C', '2004-01-02' UNION ALLSELECT 5, 'B', '2004-01-02' UNION ALLSELECT 6, 'C', '2004-01-02' UNION ALLSELECT 7, 'A', '2004-01-03' UNION ALLSELECT 8, 'B', '2004-01-03' UNION ALLSELECT 9, 'A', '2004-01-03' UNION ALLSELECT 10, 'C', '2004-01-03' UNION ALLSELECT 11, 'B', '2004-01-03'----------------------------------What I want is all the same Type which, for a same DateTransaction, as adifferent Type inserte beetween them when data is sorted by DateTransactionand NoID. In this case I would like:Type DateTransaction------ -----------------C 2004-01-02 /* B is between two C (NoID = 5) */A 2004-01-03 /* B is between tow A (NoID = 8) */B 2004-01-03 /* A and C are between two B (NoID = 9 and10) */All of these have for the corresponding date at least one transaction with adifferent type between.In the real situation NoID is an autoincrement field, the PK. And theDateTransaction hasn't any time, just a round date.Any suggestion?Thanks for your time.Yannick
View 7 Replies View RelatedNeed Help With SQL Query And Result Set.
Oct 19, 2007Good day.
I have a working knowledge of T-SQL but apparently not sufficient to solve my problem.
I have 3 tables defined as:
CREATE TABLE [dbo].[Complaints](
[ComplaintId] [int] IDENTITY(1,1) NOT NULL,
[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[DateReceived] [smalldatetime] NOT NULL,
[Classification] [tinyint] NOT NULL,
[Misconduct] [tinyint] NOT NULL,
[Status] [tinyint] NOT NULL,
[OccurrenceDateTime] [smalldatetime] NOT NULL,
[OccurrenceCityTown] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[OccurrenceRegion] [tinyint] NOT NULL,
[OccurrenceCountry] [tinyint] NOT NULL,
[CreatorId] [int] NOT NULL,
[CreatedFrom] [tinyint] NOT NULL,
[Flags] [tinyint] NOT NULL,
[Summary] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,
[Comments] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Complaints_1] PRIMARY KEY CLUSTERED
(
[FileNumber] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ComplaintSubjects](
[ComplaintSubjectId] [int] IDENTITY(1,1) NOT NULL,
[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[SubjectId] [int] NOT NULL,
[SubjectType] [tinyint] NOT NULL,
[SubjectIdentity] [tinyint] NOT NULL,
CONSTRAINT [PK_ComplaintSubjects] PRIMARY KEY CLUSTERED
(
[ComplaintSubjectId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Persons](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[MiddleNames] [varchar](64) COLLATE Latin1_General_CI_AS NULL,
[LastName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressNumber] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[AddressStreet] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressCity] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressRegion] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressCountry] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressPostalZip] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[PhoneHome] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneWork] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneMobile] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneFax] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[DateOfBirth] [smalldatetime] NOT NULL,
[Status] [tinyint] NOT NULL,
[Type] [tinyint] NOT NULL,
[DateAdded] [smalldatetime] NULL,
CONSTRAINT [PK_Personnel_1] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Basically, I track complaints and each complaint can have 1 or more subjects associated with it.
The structure is the complaint is referenced in a complaintsubject row and I use the persons table to find out detail of the complaintsubject (firstname, middlename, lastname) complaintsubject just holds Id's to normalize the database rather than have all details of persons table copied into every complaint (pretty basic so far).
My first question is regarding structure...since my complaint to complaintsubject relationship is 1 to many I assume the best way to associate the two is to have a FileNumber field in the complaintsubject linking back to a complaint. Is there any other design that is better suited to this type of relationship?
Second question and most important: I need a way to search by a string that appears in any part of the firstname, middlenames, lastname. I have the query for this and its working. But the problem arises when I have more than one match (i.e. 2 or more people are involved in the complaint where the match string fits both - example say we have a match string of 'an' and in complaint xyz 2 people involved are Sandy Blah and Andy Blue). Now when I do my join to pull out the complaints that involve any people with a name containing 'an' the result set returns the same complaint twice, one for Sandy Blah and the other for Andy Blue). Since I am only interested in certain complaint details and not interested in having the actual people involved returned in the result set, I would like to return only 1 row for each complaint when more than one match is found for the firstname,middlenames,lastname. I could live with having the duplicate rows and process them easily application side, but I am sending a lot of duplicate information over the wire for nothing and would like to optimize this.
I hope I have described this well enough and would greatly, greatly appreciate any help you can provide.
Best Regards.
Getting A Query Result Into A Textbox
Apr 17, 2008This query should always return 1 row with columns visid, cid, visdate, comment. How can I get the value of visdate in textbox1? This already works when the query is in a vb sub but I want to know how to do it this way too.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ connection string here%>" SelectCommand="SELECT * FROM [vis] WHERE ([visID] = @visID)">
<SelectParameters> <asp:ControlParameter ControlID="Label1" Name="visID" PropertyName="Text" Type="Int32" /> </SelectParameters></asp:SqlDataSource>
<asp:TextBox ID="TextBox1" runat="server" Style="left: 117px; position: relative; top: 160px"></asp:TextBox>
Column Name As The Result Of A Query?
Jan 11, 2005Simple example would look like that in MS SQL
SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)
So that the display is
English
a
as we assume that
SELECT language_name FROM language WHERE language_id = 1
returns only English
I have tried to that with a variable but it does not work
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that
LOL
I just tried another way as i was going to post
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"
and it worked!
Posting anyway so people might answer with a better solution with no variable
Thanks a lot
Mordan
Sort Result According The Query
Feb 27, 2005I have a query:
SELECT *
FROM Mobile_Subscriber
WHERE (sub_ID IN (17, 2, 19))
The result return:
sub_ID sub_name
2 John
17 Alice
19 Eddy
But what I want is:
sub_ID sub_name
17Alice
2John
19Eddy
Is it possible to return the result order by the query with: sub_ID IN (17,2,19)?
Is There A Way To Display This Query In 1 Result Set?
Feb 15, 2006I am trying to count the number of Part that is repaired and those that is not repaired, is there a way to combine the following into one result set instead of returning 2? The bold line is the only condition that's different between this 2 query.
I want to display these fields: date_complete, part_categoryid, part_model, repaired, not_repaired
/* parts being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 0 and part_flag_ntf = 0 and part_flag_beyondrepair = 0)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid,part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
/* parts completed but not being repaired */select DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0) as date_complete, part_categoryid, part_model, count(DISTINCT part_id) as not_repaired from tblPtSingapore INNER JOIN tblAuditPartStatus ON tblPtSingapore.part_Id = tblAuditPartStatus.auditpartstatus_partidwhere (tblAuditPartStatus.auditpartstatus_status = N'COMPLETE')and part_replaced = 0and (part_flag_nff = 1 or part_flag_ntf = 1 or part_flag_beyondrepair = 1)group by DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0), part_categoryid, part_modelorder by part_model, DATEADD(d,DATEDIFF(d,1,tblAuditPartStatus.auditpartstatus_datecreated),0)
Looping Through A Query Result
Jan 4, 2001I've had a thousands instances where this technique would come in handy. Can somebody help me with a way to do a select, like a join parent and child table. Then say something like:
declare @variable varchar(30)
--some kind of loop through the result set
set @variable = queryfieldresult
if (@variable is not null)
begin
select * from table where productId = @variable
end
--end loop
Thanks,
Rick
Wrong Query Result
Jul 15, 2002Hi all,
As my user runs a query for her data, the query shows up with someone else's data. Can somebody tell me what happened and how o fix the problem. Thanks!
DangKhoa
Query For A Flattened Result
May 7, 2006This is how my table was structured:
Code:
=================================================
|id|cat|amount|
|---------------|---------------|---------------|
|1|1|400|
|1|2|150|
|2|1|600|
-------------------------------------------------
I want to query for a result that would look like this:
Code:
=================================================
|id|cat1|cat2|
|---------------|---------------|---------------|
|1|400 |150|
|2|600||
-------------------------------------------------
How do I do it? I'm working with SQL Server 2000.
-Pornsak
Loop Through Result Set Using Value In Query
Mar 27, 2012I have to pull values from a mysql table, then loop through the result set using the value in an mssql query as shown below. I also have an array ($all_lobs[]) of about 100 values that must be looped through for each value pulled from the mysql table:
$tod=date("n/j/Y",time());
//this is pulling the data from the mysql table
$query2="select CustId from prospect_CustId ";
$result2=mysql_query($query2,$link_id_mysql);
while($custs=mysql_fetch_row($result2))
[Code] .....
If I pull only 100 records from the mysql table, this takes about 1 second to run. However, if I pull 200 records, it takes about 60 secs to run. And, if I pull 300 records, it takes about 200 secs to run. After about 500 records, it takes almost a second per record to run!
Since I have 20,000+ records to pull, this takes hours...
Unfortunately, we are not allowed to modify the mssql tables at all, only query them.
Result Of Query So Large?
Feb 19, 2004Hi all,
i have a problem ...
if there is a query that returns so many rows. I want to know where the result is stored? for example:what database?, what table?, what transaction log file?
Thanks fr reading.
Bcp Out Query Result To File
Jul 27, 2015I have the below query which gives me statement to apply the login name, SID and PWD and that I statement I can apply to Dr and secondary server. Incase of failover there will be no connection issue.My query is
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
[code]....
This query output I wanted in a sqlfile so that I can schedule a batch file or sqlcmd and apply the login detail from the new one inserted in the primary.
Sql Query To Get Result My Date
Jun 14, 2008hi
this seems to be a simple query. but i could not get it for the last 2 days. so please help.
i have 2 tables.1.lineup table 2.station table
Script for 2 tables
CREATE TABLE [dbo].[lineup](
[LineUpID] [int] IDENTITY(1,1) NOT NULL,
[headend_id] [int] NULL,
[station_num] [int] NULL,
[Channel_position] [int] NULL,
[CreateDate] [smalldatetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[station](
[StationId] [int] IDENTITY(1,1) NOT NULL,
[Station_num] [int] NOT NULL,
[Station_call_sign] [varchar](10) NULL,
[CreateDate] [smalldatetime] NULL
) ON [PRIMARY]
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182570','90973','19510','10','04/09/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182575','90973','10000','120','04/09/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182000','90000','12200','100','04/09/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182333','90234','12270','15','04/09/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182570','90973','19510','10','04/10/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182575','90973','10000','120','04/10/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182000','90000','12200','100','04/10/2008')
insert into lineup ("lineupid","headend_id","station_num","channel_position","createdate") values ('4182333','90234','12270','15','04/10/2008')
insert into station ("stationid","station_num","station_call_sign","createdate") values ('300','10000','ESPN','04/10/2008'
i want the result to be in this format
for the following columns.
i need all rows for headend_id = 90973 and createdate = 04/10/2008
please help
thanks in advance
the resultset column names should have :
headend_id channel_position station_call_sign station_num
Send Query Result
Feb 20, 2006Hi
How to send a Query Result as attachment in excel format using any types of sql mail tools, SQLMAIL or CDO or ....
Thanks
Wrong Sql Query Result
May 11, 200699pShop writes "I am trying to create a query to select all record for a specific 'PersonnelID'that have vacation booked in 2006 from a database.
I have written this query but it returns all PersonnelID that fit into the date block
requrirements
PersonnelID
select if DateStart falls within 2006
select if DateEnd falls within 2006
query i built:
Select * From tblResourceList Where PersonnelID=72
And tblResourceList.DateStart
Between '01 January 2006' And '31 December 2006'
Or tblResourceList.DateEnd Between '01 January 2006'
And '31 December 2006'
Or (tblResourceList.DateStart < '01 January 2006'
And tblResourceList.DateEnd > '31 December 2006')"
Question About A Query Result
Jun 26, 2007Hi Everyone, I've created a small exmaple database. The tables are:
TABLE Cocktails
CocktailID (int, PK)
CocktailName (varchar)
CocktailDescription (varchar)
TABLE Ingredients
IngredientID (int, PK)
IngredientName (varchar)
TABLE CocktailIngredients
CocktailID (int, PK)
IngredientID (int, PK)
QuantityNeeded
In this way, i can perform a query like:
SELECT
Cocktails.CocktailName, CocktailIngredients.CocktailIngredientQuantity, Ingredients.IngredientName, Cocktails.CocktailDescription
FROM CocktailIngredients INNER JOIN Cocktails ON CocktailIngredients.CocktailID = Cocktails.CocktailID INNER JOIN Ingredients ON CocktailIngredients.IngredientID = Ingredients.IngredientID
As result, I obtain the data I needed, like:
MyCocktail1 2dl Rum DescrptionOfMyCocktail
MyCocktail1 1dl Gin DescrptionOfMyCocktail
MyCocktail1 2st Lemons DescrptionOfMyCocktail
MyCocktail2 1dl Rum DescrptionOfMyCocktail
...
The name "MyCocktail1" and "DescrptionOfMyCocktail" is repeated every row, and I've been asked to find a way to avoid getting back a table with information I need only one time.
Is there something wrong in the structure of my tables? Or there is a way to perform the query in a different way and have empty fields after the first description? (of course it will be written again if cocktail name changes)
thank you a lot!