Add ID Field To A Table
Mar 9, 2006I need to know how to add an ID column, well actually a colum that start on 1 and continue counting something like a transaccion_number.
Is there a special field that I can uses to do that?
ADVERTISEMENT
TSQL - Copy Table As New Table And Get The Sum Of Specific Field Group By Other Field
Sep 4, 2007Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
Updating Fact Table Field From Source Table Field
Apr 11, 2008
Hi,
I have source table , fact table and four dim. tables , I have to update a field in fact table from source table.
How can I do it?
thanks...
Help On Updating A Field In A Table With The Field Content Of Another Table
Jun 25, 2007HI everybody need help on this..
I have two tables below
table1
country countryid
africa ___
usa ___
italy ___
Spain ___
table2
countryid country name
1 africa
2 germany
3 italy
4 usa
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
THE RESULT WOULD BE
country countryid
africa 1
usa 4
italy 3
spain 0
thanks
Selecting Field From Second Table If Same Field In First Table Is Null
Nov 25, 2007
Hi,
I have one table (PermProportionalCosting) that contains employeecode, costcentre and proportionpercentage. An employee may have two or more records in this table, indicating that their salary has been split across multiple costcentres.
Another table (Employee) contains a default costcentre for each employee.
Another table (TransActualHistoricalMaster) contains the hours paid to each employee for each pay period.
My query so far selects all employees and the total hours worked from the TransMaster table, and then selects the costcentre and proportionpercentage from the PermProportionalCosting table. Multiple lines are created for all employees in the PermProportionalCosting table, with each line indicating the proportionpercentage and associated costcentre.
This is my query so far:
SELECT TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode,PermanentProportionalCosting.PercentageSplit,
TransActualHistoricalMaster.OrdHours
FROM TransActualHistoricalMaster
INNER JOIN Employee ON TransActualHistoricalMaster.EmployeeCode = Employee.EmployeeCode
INNER JOIN CostCentre ON Employee.CostCentreCode = CostCentre.CostCentreCode
FULL OUTER JOIN PermanentProportionalCosting ON TransActualHistoricalMaster.EmployeeCode = PermanentProportionalCosting.EmployeeCode
GROUP BY TransActualHistoricalMaster.EmployeeCode, Employee.PreferredName, Employee.LastName, CostCentre.Description, PermanentProportionalCosting.CostCentreCode, PermanentProportionalCosting.PercentageSplit, TransActualHistoricalMaster.OrdHours
This next bit is what I am unsure of.
I would like the CostCentre field from the Employee table to go into the PermanentProportionalCosting.CostCentreCode column where the employee doesn't have a record in the PermanentProportionalCosting table. In the above query, there is a null value for each employee that doesn't have a record in the PermanentProportionalCosting table, so I would like my query to replace the null value with the costcentre from the Employee table.
Any assistance would really be appreciated.
Thanks.
How To Define Field Attribute For A Numeric Field In SQL Table?
Jan 19, 2005I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies View RelatedAdding A New Field To A Table With Image Field
Aug 24, 2006Hi,
I have a concern about adding a new field to a table with image field - which is huge.
Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field?
Previously this happened to me, and what was advised to put all the big fields at the end of the table.
Thanks.
Adding In A Whole Dataset Into A Table - Instead Of Field By Field
Feb 29, 2008
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
was just curious.
-dk
Moving From One Table To Other Table Automatically For Every 3 Months By Checking The Paticular Value Of The Table Field
Mar 29, 2007Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
Comparing One Table Field To Another Table Field
May 31, 2007I'm using SQL Server 2005 Express.
(Below are the three tables along with their fields)
1) tblEmailAddress Table
a) EmailAddress
b) Emailid (each EmailAddress has this corresponding Emailid)
2) tblEmailGroupLink Table
a) Emailid
b) Unsubscribed
3) Remove_List Table
a) EmailAddress
What I'd like to accomplish is...
A) To compare the EmailAddress field of all the records in the Remove_list Table to the EmailAddress field of all the records in the tblEmailAddress Table and if there is a match, get the Emailid field value.
B) Go to the tblEmailGroupLink Table and lookup the Emailid and change the value of the Unsubscribed field to 'True' for that record.
I'd appreciate any help I can get.
Thanks,
Bill
SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table
Aug 12, 2014I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedAdd Calculated Field In Order Table Based On Price Column In Product Table
Nov 18, 2014I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
Importing Access Table Into SQL Server 2005 Express Table And Adding One Field
Feb 16, 2007Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?
Aug 4, 2015I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
Aug 5, 2007I have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Jan 22, 2008Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
Transact SQL :: Update A Field In Table Based On Another Table
Nov 17, 2015I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
Selecting SUM From One Table As Field In Results Of Rows From Another Table.
Nov 20, 2007Hi,
These aren't my tables, but lets dumb it down for my sake!
I have 2 tables:
CompanyInfo: IDNumber, EffectiveDate, CompanyName
TransacationInfo: TransactID, CompanyID, TransDate, Amount
There are several records in TransactionInfo for each record in CompanyInfo.
I want to:
SELECT CompanyInfo.IDNumber, CompanyInfo.EffectiveDate, CompanyInfo.CompanyName,
SUM(TransactionInfo.Amount)
How can I do this? Can I?
Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
Sep 20, 2007I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
Nov 1, 2007I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
Feb 13, 2006We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Relation Table With Same Field From 2 Table
Jan 15, 2014I have problem for create relation between table. I have one table refer to 2 table with same field. Here's the code.
create table TopHeadConfTbl (
PartNumber varchar(14) primary key not null,
TpHdTypeID varchar(4) not null)
create table BotHeadConfTbl (
PartNumber varchar(14) primary key not null,
BotHdTypeID varchar(4) not null)
create table QuoDetTbl(
MainAsID varchar(14)not null,
PartNumber varchar(14) not null references (TopHeadConfTbl,BotHeadConfTbl) ,
ItemNumber varchar(14) notnull)
Retriving Table Name As Field In Table
Dec 28, 2007Hi,
Im using many source tables in a dataflow from which data has to be union in to a single table.While doing this i have to pull the table names in to the destination table.How to get the table names as column values?
How To Set A Key For A Field In A Data Table
Aug 10, 2006In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key.
I can easily set the first field as the primary key, but am not able to set the second field as being just a key field.
I perform these steps to set the primary key.
(1) Right click on the table name and then select 'Open Table Definition'
(2) I right click on the field in my data table and select 'Select Primary Key'
As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field:
Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties.
None of these choices will allow me to set the field up as a key field.
Someone please help me out with this problem.
SQL Table, Key, Field Design
Dec 5, 2007i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before.
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos.
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.
Reset Table Field Value To 1?
Dec 29, 2011I need an automated process where i can reset the field value to 1 on a table at the begining of each year (first day of the new year). How would you go around this? Triggers or stored procedure?
Can you have more than 1 After insert,update triggers on one table and how does it affect the performance and everything around the table.
Modifying A Field In Table?
Jan 21, 2014I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?
I have never done dropping a table and recreate it?
Duplicates In Table Field
Aug 7, 2014Im trying to look for duplicates in a table field.. field name is alphanumericCol and table is a user defined table...This is my trigger:
ALTER TRIGGER [dbo].[DUPLICATES]
ON [dbo].[AMGR_User_Fields_Tbl]
FOR INSERT, UPDATE
AS
DECLARE @Alphanumericcol VARCHAR (750)
-- This trigger has been created to check that duplicate rows are not inserted into table.
-- Check if row exists
SELECT @Alphanumericcol
FROM Inserted i, AMGR_User_Fields_Tbl t
WHERE t.AlphaNumericCol = i.AlphaNumericCol
AND t.Client_Id = i.Client_Id
-- (@Alphanumericcol = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END
The result i get is, if i input a duplicate number it fills in a null in the field so my question is how do i get it to tell me its duplicate and let me insert a new one
A Table With A Unique Field
Jul 10, 2007Hello,
I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :
The first table would contain a unique field : the key wich is an id for each faq.
The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.
What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.
But, one more time, I find a bit embarrassing having a table with a unique field...
What is your opinion about that ?
Thank you in advance for any suggestion.
mathmax
Insert Into A Table With Only One Field (ID)
Nov 25, 2007Hello,
I would like to know the sql-query to insert a new row into a table which has only one column (ID) which is the primary key.
Thanks a lot for any suggestion !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
SQL For Field Names From A Table
Oct 6, 2005Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm
View 6 Replies View RelatedTable Joins On More Than One Field
May 4, 2006Hello all,Can someone help me with this SQL?1) EMPLOYEE table has columns 'employee_id' and 'emp_sid' and othercolumns2) EMPLOYEE_BENEFITS table has a column called employee_entity, thiscolumn can be joined to either 'employee_id' OR 'emp_sid' but not bothin the EMPLOYEE table.3) EMPLOYEE_TRACK table has column called employee_track_entity, thiscolumn can be joined to the employee_benefits_id (PK) of theEMPLOYEE_BENEFITS table.I am listing the sql for the tables (the tables shows only the columnsin question)CREATE TABLE [dbo].[EMPLOYEE] ([employee_id] [int] IDENTITY (1, 1) NOT NULL ,[empsid_id] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_BENEFITS] ([employee_benefits_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_entity] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[EMPLOYEE_TRACK ] ([employee_track_id] [int] IDENTITY (1, 1) NOT NULL ,[employee_track_entity] [int] NOT NULL) ON [PRIMARY]GOSELECT * FROM EMPLOYEE eINNER JOINEMPLOYEE_BENEFITS eb ON (e.employee_id = eb.employee_entity ORe.empsid_id = eb.employee_entity)INNER JOINEMPLOYEE_TRACK et ON eb.employee_benefits_id = et.employee_track_entityThe above SQL I wrote is this: the second inner join uses a OR to joineither of the columns in the first table EMPLOYEE. There is performancedegradation with this SQL. With huge data It takes about 30 seconds toexecute. I know this is not the perfect way to do it, can anyone of theSQL Gurus please enlighten me to a faster approach?If I dont use the OR I can try left join on the same tableEMPLOYEE_BENEFITS twice by changing the join types, but If I did thiswhat table alias can I use to join to the 3rd table?SELECT * FROM EMPLOYEE eLEFT JOINEMPLOYEE_BENEFITS eb1 ON e.employee_id = eb.employee_entityLEFT JOINEMPLOYEE_BENEFITS eb2 ON e.empsid_id = eb.employee_entityINNER JOINEMPLOYEE_TRACK et ON [???].employee_benefits_id =et.employee_track_entitythanksadi[Sorry I am posting this twice, on SQL Programming forum too]
View 3 Replies View RelatedUpdate Field With Max From Another Table
Jul 20, 2005I have two tables STUDENTS and REGISTRATIONS. I wish to update mySTUDENT table field, LAST_ATTENDED_SESSION_ID from my REGISTRATIONtable of the max SESSION_ID found in the REGISTRATION table.STUDENT starts off with:STUDENT_ID LAST_ATTENDED_SESSION_ID123456789 000000234567890 000000345678901 000000REGISTRATIONS:STUDENT_ID SESSION_ID CLASS_ID123456789 200001 BUS100123456789 200102 BUS150123456789 200203 BUS160I want to update my STUDENT.LAST_ATTENDED_SESSION_ID with theMAX(SESSION_ID) found in REGISTRATIONS for each student.I'd end up with:STUDENT_ID LAST_ATTENDED_SESSION_ID123456789 200203234567890 000000345678901 000000I've found examples here about selects and sub-queries, but nothingyet in my situation with an update.TIARob
View 2 Replies View Related