SELECT Query (rows Returned In One Column)?
Aug 17, 2005
Hi there,
I¡¦ve got a table with the following as well as other info:
User ID
DirectoryTypeID (int)
Region ID (int)
I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9
If need these results to be:
subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9
Is this possible?
Many Thanks!! ļ
Grazi
View 1 Replies
ADVERTISEMENT
Jan 25, 2006
Hi,
I have a problem that I would like help with.
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Any ideas??
Regards,
Chris
View 3 Replies
View Related
Jun 14, 2006
Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? (without sending it to page)
Here is it:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif
Thank you
Artashes
View 12 Replies
View Related
Jul 31, 2007
How is the best way to check for No rows returned from a Select statement in a Stored Proc? I have see this:
Code Snippet
Declare @res int
set @res = NULL
Select @res = dbKeyColumn
From aTable
If @res is null
then no rows returned.
Surely there is a better way??
Thanks.
View 3 Replies
View Related
Jan 12, 2005
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View 6 Replies
View Related
Nov 27, 2013
I have a query that returns a bunch of rows. I have an sp that takes 3 of the columns in the query as parameters that I want to execute for each row. I can do that easily enough with a cursor, but I thought I'd try to eliminate one more task where I fall back on using cursors.
View 5 Replies
View Related
Oct 19, 2006
I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?
View 3 Replies
View Related
Jul 30, 2013
I want to show the number of rows returned by a select query in the result.
e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy
If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result
OutPut
0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..
How can i get this number in the result.
View 3 Replies
View Related
Dec 13, 2007
Hi all,
I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.
View 1 Replies
View Related
May 17, 2008
Hi
In a query in Management Studio, I want to output data to a text file (via Results in text button) without column names and hyphens or dashes, I have searched all SET commands with bad luck because SET FMTONLY OFF do exactly the opposite.
I know this could be a silly question because I have received suggestions in other ways to do that, but I am intrigued If there is a way to prevent column name from being displayed/returned from a query.
@@version yields
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Thank you very much, best regards
japonce
View 4 Replies
View Related
Mar 30, 2004
hi all
Any day, another question. I love you guys.
I want to select a subset of data from a dataset returned by either another subquery or a union.
e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.
select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'
in essence oracle treats the data returned by the subquery as a table that it would select from.
how would i do the same in mssql?
thank you
James :)
View 5 Replies
View Related
Sep 11, 2015
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
View 4 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Jun 29, 2005
G'day,
I have a datetime column that holds dates and times like
30/06/2005 1:31:00 PM
How would I find all rows that match a certain date regardless of the time, (IE, select all rows with a date of 30/06/2005 with any time)
Thanks,
Robbo
View 4 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Jul 26, 2013
CREATE TABLE [dbo].[x1](
[nomer] [varchar](15) NOT NULL,
[tgl] [datetime] NOT NULL,
CONSTRAINT [pk_x1] PRIMARY KEY CLUSTERED
(
[nomer] ASC,
[Code] ....
Result :
nomer tanggal
12013-07-28 00:00:00.000
12013-07-29 00:00:00.000
12013-07-30 00:00:00.000
I Want to make :
nomer tanggal
1 28,29,30
View 2 Replies
View Related
Apr 17, 2014
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
Some of the data is:
1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015
but there is a mix of alphabetical characters in there as well.
1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
View 9 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Apr 5, 2007
Hello.
I have a table with a datetime column.
I need to select rows with a NULL or a datetime earlier than 2 hours
ago if getdate() time is currently between 6am and 7pm.
Thanks for any help or information!
View 1 Replies
View Related
May 2, 2015
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[giftregistryitems](
[id] [int] IDENTITY(1,1) NOT NULL,
[code].....
View 0 Replies
View Related
Apr 27, 2008
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View 2 Replies
View Related
Jan 16, 2008
I have detail table like
date item_id grade in out
------ ------- ------- ----- -----
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8
Result View
=======
Item_id A B O T Total
------- --- ---- --- --- -------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2
I want result group by Item_id and sum of grade in column .
where grade could be any Alphabet, the column of result query could varies.
Each grade contains sum of (in-out) of item_id of detail table.
Smartsys99
View 1 Replies
View Related
Jul 23, 2005
HiYour help for the following query will be highly apprecaited. I'vewasted alot of time on it. Data definition is at the bottom.Thanks-----------------------------------Business need: User selects answers for few questions. Answers arepresented in the form of radio buttons.Questions Table:===================QuestionID QuestionName1 1.Rate your organization2 10.Opportunity in your group3 117.Effectiveness----------------------------------------Answers Table:==============AnswerId AnswerName QuestionID1 1.Best 12 2.Average 13 3.Wrose 14 1.Hardly Any 25 2.not too much 26 3.Average 27 4.Great Deal 28 1.Strong 39 2.Minor 310 3.Nothing 3--------------------------------------------------------Lets say User ABC answered below:For Question 1, she chose '2.Average'For Question 2: she left blankFOr Quesiton 3: she chose '2.Minor'And for user XYZ:For Question 1, she let blankFor Question 2: she left blankFOr Quesiton 3: she chose '3.Nothing'Then the Results table will look like this:Results Table:===============Resultsid QuestionID AnswerId User1 1 2 ABC2 3 9 ABC3 3 10 XYZ---------------------------------------------------Desired Output:===============User 1 10 117--- -- --- ----ABC 2 2XYZ 3Explanation:--> Show ALL distinct users on rows--> show ALL distinct QuestionName on the columns BUT only display thenumber before '.'--> Show the chosen answer (answername) for each user but only displaythe number before '.'-------------------------------------------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[Questions]GOCREATE TABLE [dbo].[Questions] ([QuestionId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionName] [nvarchar] (1000) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO-------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Answers]GOCREATE TABLE [dbo].[Answers] ([AnswerId] [int] IDENTITY (1, 1) NOT NULL ,[AnswerName] [nvarchar] (150) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[QuestionId] [int] NULL) ON [PRIMARY]GO----------------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Results]GOCREATE TABLE [dbo].[Results] ([ResultId] [int] IDENTITY (1, 1) NOT NULL ,[QuestionId] [int] NULL ,[AnswerId] [int] NULL ,[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GO---------------------------------------------INSERT Answers (AnswerName ,QuestionId)VALUES ('1.Best' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Average' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Wrose' ,1)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Hardly Any' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.not too much' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Average' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('4.Great Deal' ,2)goINSERT Answers (AnswerName ,QuestionId)VALUES ('1.Strong' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('2.Minor' ,3)goINSERT Answers (AnswerName ,QuestionId)VALUES ('3.Nothing' ,3)go-----------------------------INSERT Questions (QuestionName)VALUES ('1.Rate your organization')goINSERT Questions (QuestionName)VALUES ('10.Opportunity in your group')goINSERT Questions (QuestionName)VALUES ('117.Effectiveness')go------------------------------------INSERT Results (QuestionId ,AnswerId ,UserId)VALUES (1 ,2 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,9 ,'ABC')goINSERT Results (QuestionId ,AnswerId ,UserId)VALUES (3 ,10 ,'XYZ')go
View 2 Replies
View Related
Nov 19, 2004
Hi,
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
Thanks,
Alph
View 3 Replies
View Related
Jun 12, 2007
I am using SQLExecute() in C++ code.
My query is "select * from Revoked_users"?
How I will come to know the no. of rows returned by SQLExecute() ?
View 4 Replies
View Related
Apr 7, 2008
The code below returns 0 rows. The statement is intended as generic statement that would return all records between a particular start and end date and on each date only between a specific start and end time. It works perfectly if the end date is greater than the start date.
Unfortunately, if the start and end dates are equal (i.e, return all records on that one date and only between the specified start and end times) then no records are returned.
BTW, it is also looking for matching datetimes in two tables and there really is matching data in the two tables for that particular date and times.
Can anybody help resolve this?
SELECT DISTINCT t1.* FROM [DbName].[SchemaName].[TableName1] AS t1,
[DbName].[SchemaName].[TableName2] AS t2
WHERE t1.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME)
AND t2.[DateTime] BETWEEN CAST('2006-11-22' AS DATETIME) AND CAST('2006-11-22' AS DATETIME) AND
CONVERT(DATETIME,CONVERT(varchar, t1.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
CONVERT(DATETIME,CONVERT(varchar, t2.[DateTime],114)) BETWEEN
CONVERT(DATETIME, '10:20:00') AND CONVERT(DATETIME, '12:19:59') AND
t1.[DateTime] = t2.[DateTime]
ORDER BY t1.[DateTime];
View 11 Replies
View Related
Oct 15, 2007
I have a qry/dataset in a report. If the qry returns no data the report will
simply show a the headers/footers and no data in the detail section. However, I need to display all zeros if the detail section ie '0', if no data is returned. How can I do that?
I have SSRS 2005.
View 1 Replies
View Related
Jun 28, 2002
Message:
Hi,
Suppose i execute a query,
Select * from emp,
I get 100 rows of result, i want to write a sql query similar to the one available in MySql database where in i can specify the starting row and number of rows of records i want,
something like,
select * from emp LIMIT 10,20
I want all the records from the 10th row to the 20th row.
This would be helpful for me to do paging in the front end , where is i can navigate to the next previous buttons and fetch the corresponding records.
I want to do something like in google, previous next screens.
So I am trying to limit the number of rows fetched from a query.
somethin like,
select * from emp where startRowNum=10 and NoOfRecords = 20
so that i can get rows from 10 to 30.
Has any1 done this, plz let me know.
Cheers,
Prashant.
View 1 Replies
View Related
Jul 23, 2005
I have a table of zip codes, some with Canadian zips. I'd like to takea zip code and search for nearby zips. For example:Dim theZip As Integer = textbox1.text....Parameter.Add(@ziplow, SqlDbType.Int, 5).Value = (theZip - 10)....Parameter.Add(@ziphigh, SqlDbType.Int, 5).Value = (theZip + 10)SELECT * from ZIPCODES where Cast(zip_code as Integer) BETWEEN @lowzipAND @highzipProblem is the letters in the Canadian records cannot be cast asintegers for this process. I get this error:Syntax error converting the varchar value '53151 1' to a column of datatype int.Is there a SQL query that can exclude if no cast can be made to anInteger?Thanks!Anton
View 5 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 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
Feb 3, 2008
Hello and thank you taking a moment. I have created a simple login page where the user will pass credentials. I have a sqldatasource that will query a database to see if the user exists. What I would like to do is have the user click a button after entering their credentials. When the button is clicked I would like the SqlDatasource SelectCommand to fire and if there are rows returned then redirect the user to a new page.
I know I can do this with ADO and a datareader with the HasRows property. But what I would eventually like to do is cache the data and then bind the cached data to a control(like a dataview) on the page the user is redirected to. If anyone can tell me how to get the Select command to fire on a button click I would be eternally grateful. Any help will be greatly appreciated.
Thanks,
Jason
View 1 Replies
View Related
Jun 3, 2004
I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?
View 2 Replies
View Related
