Table 1
-------
NAME GROUPID
AAA 1
BBB 2
Table 2
-------
NAME GROUPID
CCC 1
DDD 1
EEE 2
Name & GroupID are column names.
I want to query these tables and get a result like that:
AAA CCC
DDD
BBB EEE
-----------
NOT
-----------
AAA CCC
AAA DDD
BBB EEE
Is it possible? If so, how?
Thanks in advance.
HI,
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
Good morning,
I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.
the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?
thank you,
As 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
I am using SQL server 2005. I have a VIEW that joins several tables. One of the table's column can be added dynamically by the user from a GUI interface. However, after a column is added, it does not show up in the VIEW immediately. It will take a while (I haven't figured out exactly how long) before the extra column shows up as the execution result of the VIEW.
So it seems like SQL server is caching that VIEW's schema. Is there anyway I can make this view always comes back with the latest schema?
Thanks a lot!
Penn
Good morning all,
I created a view that selects the rows of the records from a table that matches where condition.
I need to export the records to excel so I could send it out....
what is the best way to do?
Please tell me detail, I don't know much about SQL 2000, still learning.
Thank you,
Yanoroo
I am trying to export the results of a view from an .adp to an .mdbusing TransferDatabase. The code I am using isDoCmd.TransferDatabase acExport, "Microsoft Access", _"C:\___workWayAheadImplementationXTRA_Samples.md b" _, acTable, "x_loe_vw", "LOE_tbl"When I run this I get an error that states" can't find the object 'x_loe_vw'".Yet it is defined as a view.Any ideas?Thanks,Jerry
View 2 Replies View RelatedI have a view with 2 columns and 2 rows(No 1). I want to show the result with 3 columns in 1 row(No 2).
No1:
The view result :
ID | SubjectID
-- ---------
13279 | 900
13279 | 910
No2:
I want to show that result in this structure :
ID | SubjectID | SubjectID2
-- --------- ----------
13279 | 900 | 910
In SQL 2005 - when I display the results of a View and Copy all rows and columns, the resulting Paste in Excel does not include the column names.
How can I set SQL 2005 so the names of the columns will come along with the content of the copy function?
Background:
When I am using a SQL Query (instead of a view) I have the ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
Thank you,
Poppa Mike
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedI 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!
When 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...
I 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 RelatedI 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] ....
I'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
We 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
Hi,
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');
Hi 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 Relatedmail_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 RelatedHi 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 RelatedHi,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
hi,
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.
I 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 RelatedHello 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 RelatedGood 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.
This 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>
Simple 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
I 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)?
I 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)
I'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
Hi 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
This 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
I 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.