Help With (Pivot/Cross-Join???) Query To Select A Result Set
Jan 20, 2005
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity
-----------------------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16
-------------------------------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
I have the following 2 tables:(BATCHES)BatchID [int] KEYID [int]OrderID [int]Action1DateTime [datetime]Action2DateTime [datetime]Action3DateTime [datetime]Action4DateTime [datetime]Action5DateTime [datetime]Action6DateTime [datetime]Action7DateTime [datetime]Action8DateTime [datetime](ORDERS)OrderID [int] KEYProductionLineID [int]RecipeID [int]OrderAmount [int]Batches.Action1DateTime to Batches.Action8DateTime can have several entrieseach day.I need a query to count all Batches.Action1DateTime to allBatches.Action8DateTime for each day in a specified period.I also need to specifically use where clauses for Orders.OrderID and/orOrders.RecipeID.I need the data to draw a graph for each ActionXDateTime as a function ofdate.Any help appreciated./Henrik
I have the following tables:members--------------member_idmember_tpc_id ( = tpc.tpc_id)tpc------tpc_idcourse------------course_idtpc_assignment---------------------------tpc_assignment_idcourse_idenrollment-------------------member_idcourse_idenrollment_status Now I want to select all members where member_tpc_id>0 and get the enrollment_status of each member in each course where course_id IN (Select course_id From tpc_assignment)Now what i did was get all the members and then all the courses and did a cross join between them. There are about 1900 members and 80 courses and when I do a cross join I get 1900*80 rows (152000) and the status of each member for all the 80 courses. If not enrolled it returns Not Enrolled (i have a UDF which takes a member_id and a course_id and returns the status). The BIG problem is that its taking about 6-8 mins to run the query and as a result its timing out on the aspx page. Can someone please tell me how I can do what i am trying to do without using the cross join because I suspect its the culprit here. The query I came up with is Select *, dbo.returnStatus(temp1.user_id, temp2.course_id) As Status, (Select tpc_title From tpc Where tpc_id = temp1.member_tpc_id) As Tpc_Title From (Select member_id As user_id, member_name, member_tpc_id From members Where member_tpc_id> 0 And organization_id = '1' )temp1 cross join (Select course_id As course_id, course_title As course_title From course Where course_id IN (Select course_id From tpc_assignment Where tpc_requirement_id IN (Select tpc_requirement_id From tpc_requirement) And course_id<>0 And organization_id = '1') )temp2 Order By member_name, Tpc_TitlePlease help. Thank you.
Hey All... Got a View question. Have 2 tables: #1 Currencies |CCY_Name|CCY_Code|
#2 Rates |CCY1|CCY2|CCY3|...etc|Active| -> where the Columns CCY# = the Records in #1
How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that -- two queries against a columnstore index that each execute in batch mode -- will continue to execute in batch mode when inner joined. -- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows IF OBJECT_ID('dbo.IntCount') IS NOT NULL DROP FUNCTION dbo.IntCount;
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
insert into @log(date, category, value1, value2) select '2008-01-01', 'ABC', 11,12 union all select '2008-01-02', 'ABC', 35,53 union all select '2008-01-03', 'ABC', 38,62 union all select '2008-01-05', 'ABC', 59,95 union all select '2008-01-02', 'XYZ', 42,21 union all select '2008-01-04', 'XYZ', 9,7 union all select '2008-01-05', 'XYZ', 89,45 union all select '2008-01-01', 'HHH', 70,52 union all select '2008-01-03', 'HHH', 3,83 union all select '2008-01-05', 'HHH', 26,77
where 1) date is always up to the day (no time variation) 2) date and category can be considered a composite unique key
Given a date range (let's say, from 2008-01-01 to 2008-01-05) I need to get the below:
Ideally, the results include - every day in the date range (even if there is no corresponding data for that date) - the columns values to be dependent on the categories found within the date range
I came up with this
-- to fulfill requirement "every day in the date range" declare @dt table (d smalldatetime) insert into @dt select '2008-01-01' union all select '2008-01-02' union all select '2008-01-03' union all select '2008-01-04' union all select '2008-01-05'
-- to fulfill display all the categories (manually determined) select convert(varchar(10),d,101) as date, abc.value1 as abc_value1, abc.value2 as abc_value2, xyz.value1 as xyz_value1, xyz.value2 as xyz_value2, hhh.value1 as hhh_value1, hhh.value2 as hhh_value2 from @dt dt left join @log abc on dt.d = abc.date and abc.category = 'ABC' left join @log xyz on dt.d = xyz.date and xyz.category = 'XYZ' left join @log hhh on dt.d = hhh.date and hhh.category = 'HHH'
just for the purpose of generating the end result example, but in a real life situation, both the date range and the categories that may fall within that date range... are dynamic. To make my head spin even more, I also suspect the issue of value2 AND value3 being pulled is making this one complicated statement.
TABLE [dbo].[Tbl_Products]( [Product_ID] [int] IDENTITY(1,1) NOT NULL, [Product_Name] [nvarchar](50) NOT NULL, [Catagory_ID] [int] NOT NULL, [Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes, A.BuyPrice,A.TotalPrice,A.BuyPrice FROM Tbl_Products B INNER JOIN Tbl_PurchaseHeader C ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A ON A.PurchaseOrder=C.purchaseOrder WHERE A.PurchaseOrder=3
From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF
XSection (Width, Height, Flange, Leg, LegCount)
I tried the following to get a pivot table but it does not give a single row but 5.
SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width, CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height, CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange, CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg, CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp ON tsp.fkProperty = sp.Property WHERE tsp.fkTemplate = 1
So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?
Code Snippet DECLARE @start_date datetime, @weeks int
SET @start_date = '1/1/2007'/*Set this to the date you want your analysis to begin on. Be mindful of the date you pick. For instance, in this case we want the beginning of the week to always be a Monday so we are OK picking 1/1/2007 because it is a Monday.*/ SET @weeks = 12
SELECT DATEADD(dd, (n.Num-1) * 7, @start_date) [Beginning of Week], /*This is creating the Beginning of each week. Looks at each record in the Number table, subtracts one off of the Num field and then multiplies by 7. Takes the resulting number and adds that many days to our start date variable.*/ DATEADD(dd,(n.Num * 7)-1,@start_date) [End of Week], /*This is creating the Ending of each week. Looks at each record in the Number table, muiltplies it by 7 and then subtracts one off of the result. Takes the resulting number and adds that many days to our start date varaible.*/ ( /*This is where we are counting the number of opportunities in each week. This returns a reasulting count for each combination that is generated in the outer query.*/ SELECT COUNT(*) FROM op_opportunity op_o WHERE op_o.open_date BETWEEN DATEADD(dd, (n.Num-1) * 7 , @start_date) AND DATEADD(dd, (n. Num * 7)-1, @start_date) /*Count if the open date is between what we've identified as the Beginning of Week and End of Week for each iteration.*/ ) AS [Opportunities Open] FROM Numbers n WHERE DATEADD(dd,n. Num * 7,@start_date) >= DATEADD(wk, -@weeks+1, GETDATE()) AND DATEADD(dd,n. Num * 7,@start_date) <= DATEADD(wk, 1, GETDATE()) /*We are just limiting our evaluation to anything less than todays date and within the number of weeks in our @week variable.*/
It returns a result set that looks like this:
Code Snippet Beginning of Week End of Week Opportunities Open 11/5/2007 12:00:00 AM 11/11/2007 12:00:00 AM 369 11/12/2007 12:00:00 AM 11/18/2007 12:00:00 AM 326 11/19/2007 12:00:00 AM 11/25/2007 12:00:00 AM 203 11/26/2007 12:00:00 AM 12/2/2007 12:00:00 AM 333 12/3/2007 12:00:00 AM 12/9/2007 12:00:00 AM 421 12/10/2007 12:00:00 AM 12/16/2007 12:00:00 AM 286 12/17/2007 12:00:00 AM 12/23/2007 12:00:00 AM 411 12/24/2007 12:00:00 AM 12/30/2007 12:00:00 AM 48 12/31/2007 12:00:00 AM 1/6/2008 12:00:00 AM 234 1/7/2008 12:00:00 AM 1/13/2008 12:00:00 AM 314 1/14/2008 12:00:00 AM 1/20/2008 12:00:00 AM 309 1/21/2008 12:00:00 AM 1/27/2008 12:00:00 AM 207
What I want to do is change this so that I can get a count of new opportunties by week but also by rep. In the op_opportunity table there is a field called sales_owner that I would use. Ideally, I also want to have it Pivoted so that the weeks are columns and the sales_owner are the rows. I tried using the PIVOT command but you have to explicitly indicate your columns. Because the number of weeks that are chosen could always be different I can't do that.
Ultimately, this will be presented through reporting services so I can probably use the Cross Tab tool to show it the way I want, so if I could just get the same result set but instead the rep added and the opportunities they created that would probably work. So if we had 12 weeks and 10 reps that would produce 120 rows.
I also hardcoded the @startdate to 1/1/07 because that is a Monday and it is back far enough in the past that I don't have to worry. As we get further away from that date will that cause any performance issues?
Hi All, The problem is about cross reference. 1. I have a third party cross reference store procedure SimpleXTab CREATE PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */exec sp_executesql @sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor open xcursor Fetch next from xcursor into @Col While @@Fetch_Status = 0Begin set @Sqlstr = @Sqlstr + ", " set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col + "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'') set @Sqlstr = @tempsql Fetch next from xcursor into @Col End /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */ set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable + @XWhereString + ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql Close xcursor Deallocate xcursor set @tempsql = N'Drop Table ##temptbl_Cursor' exec sp_executesql @tempsqlprint @tempsql /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr exec sp_executesql @Sqlstr if @@rowcount = 0 select 'No Records found'GO 2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function. how can i modify SimpleXtab to avoid using math function but still can generate cross reference. exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
Currently my select will return multiple districts for each of the users. I would like to combine the multiple districts into one string field.
Current proc:
ADName DistrictID
Glenn Stalions 9
Bob Smith 9
Pam Cassidy -1
Shannon Sanchez 1234
Shannon Sanchez 1355
Change to:
ADName DistrictID
Glenn Stalions 9
Bob Smith 9
Pam Cassidy -1
Shannon Sanchez 1234, 1355
If the user is managing more than one district the proc would return a single feild with a csv string of all districts.
I have tried pivot's and it returns multiple columns which I don't want. In addition, I have to hard code 300+ district id's to get the pivot to work correctly.
Something like this will work but I want to call it each row and not for the entire proc: declare @csv varchar(max) (SELECT @csv = coalesce(@csv+', ','' ) + cast(districtid as varchar) FROM #district) select @csv
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...
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
in table Databackup company keepmonth ----------------- ------------------- 001 12002 12003 6005 607917 609747 6
I run this query select Max(keepmonth) as keep from Databackup why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6 does my query error? thank you
Till now I get data form multiple table using join, but unable to understand how can i get the this result based on given table -
Result should be -
ProCodeProductName PRO00001;PRO00002Product Test SearchedPromotion One;Promotion Two PRO00001;PRO00002;PRO00002Product Final SearchedPromotion One;Promotion Two;Promotion Three PRO00002TestingPromotion Two
Tables - select * from ProMaster CodeName PRO00001Promotion One PRO00002Promotion Two PRO00003Promotion Three
select * from ProDetail IDProCodeProduct 1PRO00001;PRO00002Product Test Searched 2PRO00001;PRO00002;PRO00002Product Final Searched 3PRO00002Testing
The value of one of the columns in my table is 14000 lines(678913 characters). The datatype of that column is varchar(MAX). I am doing the following select query but its truncating the results.
select value -- this is truncating the text. from dbo.GUISETTINGS
The length is shown as below when I do the query:
SELECT DATALENGTH(VALUE) from dbo.GUISETTINGS -- return 707951 as the length.
I even tried running the query below but still the value is getting truncated. However, if I right-click and select "Save Results As" a file, then it shows all the lines/characters fine.
select value, cast(value as text), cast(value as varchar(max)) from dbo.GUISETTINGS
Hello, I have an SQL problem that is hard to describe so here's an example scenario:
a) company has a personnel database, identifying each person in the company and their assigned role(s).
b) tblPersonnel has a row per person, key is 'PersonnelID' c) tblRoles is a list of all the roles in the company (administrator, clerk, shipper, truck-driver, etc.), key is 'RoleID'
d) tblPersonnelRoles is a cross-reference of all the roles assigned to each person - in simple format of:
PersonnelID | RoleID
Given all the above, I need to have a select statement that produces one row per person, and list all the fields from tblPersonnel AS WELL AS identifies each role assigned to the person.
I'm no SQL guru .. is this even possible?
I know there are alternative solutions (e.g., to write code that for each Personnel does a lookup in Roles) but for one particular situation I need a single SQL statement, or even Stored Procedure.
Thanks in advance, Rick Piovesan Detaya Corp Aurora, Ontario, Canada
Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:
I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.
I am a newbie to SQL Server. I have a problem, in filtering the records returned by a query. I have a table which contains 1 million records, it has a user defined primary key which is of character type. The problem is i need to filter the output of a select query on the table based on two parameters i send to that query. The first parameter will be the starting row number and the second one is the ending row number. I need a procedure to do this.
For Eg: MyProc_GetRowsFromBigTable(startRowNo,endRowNo) should get me only the rows in the specified range.
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
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
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');