Adding Row Number To A Query Result
May 22, 2008
Is there any way I can add record number to my query result?
Exp:
Name Add tel
A 1 st 123456
B H ave. 987456
I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456
Thanks
View 9 Replies
ADVERTISEMENT
Apr 23, 2004
I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?
View 4 Replies
View Related
Sep 1, 2004
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X
In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
Great thanks
View 1 Replies
View Related
Mar 5, 2015
I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1
Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022
[Code] ....
I want the result also no ordered by Field1. it just as the natural order of the table..
View 3 Replies
View Related
Feb 13, 2001
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
View 1 Replies
View Related
May 1, 2008
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
View 3 Replies
View Related
Oct 25, 2006
In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.
View 1 Replies
View Related
Jan 19, 2006
Hi All
I have an existing table of students, the school now has decided to give then a General Record No.The table structure is as follows
Students
++++++++++++++
Name
Address
TelNo.
++++++++++++++
I have modified the table as
Students
++++++++++++++
GRN.
Name
Address
TelNo.
++++++++++++++
The data already exists in the table . I just want to add the GRN No. that would be incremental
E.g.
it should be 20060001 for the 1st record
20060002 for the second
The rest data should be as it is
Plz help
Thanks
View 1 Replies
View Related
May 30, 2008
like (###) ###-####.
I know that
SELECT phone
FROM author
however, the results will be like
408 496-7223.
but I do not know how to format the phone number.
can you help me to figure it out please?
thanks.
View 4 Replies
View Related
Nov 13, 2007
Hello, I am very, very new to the world of SSIS and from a totally different platform (iSeries), learning, digging and finding golden nuggets as I go. If this is not the forum to which I should be addressing my dilemma, please let me know as to whom or where I should direct it to. I have presented this question at two other forums, and I find it difficult to believe that no one is able to help me.
My two questions are as follows:
Question One:
I am presently encountering the following exception.
SSIS package "DW.CUST_CNCL_ORD.dtsx" starting.
Error: 0xC00291E2 at BP Parter Exec SQL, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Task failed: BP Parter Exec SQL
Warning: 0x80019002 at DW CUST_CNCL_ORD: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DW.CUST_CNCL_ORD.dtsx" finished: Failure.
Try as I do to find information regarding this exception, I am not able to. Can anyone share their wisdom on this topic?
Question two:
Is there a book that you may recommend that will contribute to my learning this new platform and evironment? I presently have the SQL Server 2005 integration services book.
Sample code follows:
SELECT BUS_YY_ID AS RBUS_YY_ID, BUS_PRD_ID AS RBUS_PRD_ID, BUS_PRD_STRT_DT AS RBUS_PRD_STRT_DT,
BUS_PRD_END_DT AS RBUS_PRD_END_DT
FROM BP.BUS_PRD_DT
WHERE (BUS_YY_ID = ?)
The first parameter is defined as parameter 0 - Direction is Input
Subsequent parameters are defined as follows:
Variable Name Direction Parameter Name Current_Year Input 0
RBUS_YY_ID Output 1
RBUS_PRD_ID Output 2
RBUS_PRD_STRT_DT Output 3
RBUS_PRD_END_DT Output 4
Result is defined as follows:
Result Name Variable Name
1 RBUS_YY_ID
2 RBUS_PRD_ID
3 RBUS_PRD_STRT_DT
4 RBUS_PRD_END_DT
Thank you in advance for any information you may provide.
Crodrig
View 26 Replies
View Related
Nov 3, 2006
Hello,
Nice easy one (hopefully) from a newbie on SQL 2000.
I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;
TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No
I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.
I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,
So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.
What simple thing I am not doing ?
Cheers
Neal
View 2 Replies
View Related
Jul 4, 2012
Code:
SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
View 3 Replies
View Related
Nov 25, 2013
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
[Code] ....
For the somenum column I need to add alphabets to make them unique. for example
121a,121b,121c....121z,121aa,121ab,101a and so on...
View 4 Replies
View Related
Sep 3, 2014
add a number to the end of an ID to create a series.For example, I have an EventID that may have many sub events. If the EventID is 31206, and I want to have subEvents, I would like have the following sequence. In this case, lets say I have 4 sub Events so I want to check the EventID and then produce:
312061
312062
312063
312064
How can I check what the EventID is, then concatenate a sequence number by the EventID?
View 9 Replies
View Related
Jul 20, 2005
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
View 1 Replies
View Related
Sep 20, 2007
I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings
returned for the ResultSetType: "ResultSetType_Rowset".
Anyone have any ideas here? Thanks in advance!
View 2 Replies
View Related
Jun 9, 2006
Hi there,
This seems a bug to me. Or does anyone has a logical explanation that escapes me?
When in SSIS Designer Version 9.00.1399.00 I add output columns (numeric 4,0 ) to a scriptcomponent and fill them with valid numeric data in thescript I get a database error 'invalid number' when I use these columns in an OLE db Command-transformation . This errormessage disappears when I replaces those columns by a dataconversion to the datatype they originally have.
Derived Column Name Derived Column Expression
STATUS_DEF Replace 'STATUS_DEF' (DT_NUMERIC,4,0)STATUS_DEF
Maybethis info is usefull for somebody else who can't figure out wathever he's doing wrong.
Paul Baudouin
View 1 Replies
View Related
Nov 19, 2015
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
View 20 Replies
View Related
Nov 7, 2007
I 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!
View 1 Replies
View Related
Jul 30, 2007
I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
View 10 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Feb 25, 2012
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...
View 3 Replies
View Related
Apr 9, 2006
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 Related
May 9, 2015
I 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] ....
View 4 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
Jun 3, 2004
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
View 1 Replies
View Related
Dec 10, 2007
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
View 7 Replies
View Related
Jan 28, 2008
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');
View 7 Replies
View Related
May 22, 2007
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 Related
Mar 15, 2008
mail_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 Related
Oct 29, 2005
Hi 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 Related
May 17, 2005
Hi,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
View 5 Replies
View Related