InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 07:32:26 GMT20http://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxI just want to say all of you who helped me out.Regarding the NULL values I decided to fill it with blank values by simply putting this statement onlineUPDATE PERSONSSET Father_Occupation = ''WHERE Father_Occupation IS NULLGOIt worked for me, now I don't see those NULL values anymore.Laurie was right about understanding my case, I just needed an app for printing one record from original baptism bookSun, 07 Oct 2012 07:05:31 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]laurie-789651 (8/15/2012)[/b][hr]I read it as: the book/page/entry data refers to the original hand-written books, and the data is just required to print on the certificate so it can be referred-back to the books if required.[/quote]you may well be correct...I didnt read it that way originally....but can now see your viewpoint.we can only wait and see if the OP has a "view" on this.Wed, 15 Aug 2012 14:32:52 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxI read it as: the book/page/entry data refers to the original hand-written books, and the data is just required to print on the certificate so it can be referred-back to the books if required.Wed, 15 Aug 2012 07:57:40 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]priest Ivan Delic (8/13/2012)[/b][hr]Hi!I forgot one thing. In paper-data there is about 30 books with 100 pages in each of them. On each page there is 10 records, so 1,000 records per book, equally about 30,000 records all.In app that was given to me, I have assignment to print only one record on printing form (which is called in Serbia Baptism paper, I don't know in English how to call it). So I need to print one record on one page with all data for that record (book, page, number, dates, name, parents, priest, godfather, about 15 fields). As for ID field, it is PK, Auto-increment and not changeable in app.Sincerely yours,Fr. IvanSerbia[/quote]I make a small observation.....it would appear from what you have said that that the printing app requires you to provide the order of book/page/number etc prior to print....by linking to the RECORDS table on ID ...it seems you only have one method of sort order?what are you going to do if, for instance you decide to change your mind and print 5 records per page, book - place of baptism - chronological order of dob?I am not sure that the code will give that you a degree of flexibility going forward...but thats for you to decide....or of course, I may have misunderstood :-)kind regards Tue, 14 Aug 2012 14:36:45 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxIf you add a NULL into a string, the result is NULL, so you need to handle the nulls individually like this:ISNULL(first value, second value) - returns the first non-null value. COALESCE can handle more than 2 values - I don't think you need this though.So you would use:[code="sql"]Select ISNULL(Father_FirstName + ' ', '') + ISNULL(Father_LastName + ' ', '') + ISNULL(Father_Occupation... AS Parent[/code]You only need to deal with the columns which contain nulls (of course).Note: The way it's written stops too many spaces being entered in the string.Tue, 14 Aug 2012 10:41:57 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]priest Ivan Delic (8/14/2012)[/b][hr]Thanks laurie!There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).When I executed T-SQLSelect Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS ParentResult was NULLcause of NULL data in Father_Occupation field (column).I will try, however, to work it out on my own. Thanks for the help.[/quote]Try select IsNull(Father_FirstName,'') + ...which replaces NULLs with empty strings and should make things work.Tue, 14 Aug 2012 10:40:11 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThanks laurie!There is something else but I think I could work it out. One of the problem was a thing that I didn't entered few row data, I left them NULL (for instance Father occupation, mother birthname - those data wasn't entered in original paper data that was given to me).When I executed T-SQLSelect Father_FirstName + ' ' + Father_LastName + ' ' + Father_Occupation... AS ParentResult was NULLcause of NULL data in Father_Occupation field (column).I will try, however, to work it out on my own. Thanks for the help.Tue, 14 Aug 2012 10:32:48 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThank you.One thing - using INNER JOINs assumes the ID exists in all tables. If it doesn't you will lose the whole record. If any table is missing IDs, you can join with an OUTER JOIN instead, to retrieve the data that is in the other tables.If you need anything else post it here & I'll try to answer tomorrow.Mon, 13 Aug 2012 13:39:00 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThanks laurie!This is exactly what I wanted to do. Thanks a lot.Very grateful, wishing you and yours all the best from our Lord Jesus Christ and Our Father who are in Heaven,Fr. Ivan SerbiaMon, 13 Aug 2012 12:55:42 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxHi!I forgot one thing. In paper-data there is about 30 books with 100 pages in each of them. On each page there is 10 records, so 1,000 records per book, equally about 30,000 records all.In app that was given to me, I have assignment to print only one record on printing form (which is called in Serbia Baptism paper, I don't know in English how to call it). So I need to print one record on one page with all data for that record (book, page, number, dates, name, parents, priest, godfather, about 15 fields). As for ID field, it is PK, Auto-increment and not changeable in app.Sincerely yours,Fr. IvanSerbiaMon, 13 Aug 2012 12:50:06 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxYes, you are right want I need to do.I started to create MS Access database with those different tables knowing for normalization. My printing app need only one table as data Source.Of course I have my original Access DB, but I am little bit familiar with SQL Server when it comes of joining two or more fields in one new field, than I was familiar and know to do it in MS Access.My app printing report should look like:Book 1Page 1Number 1Birthdate 1980. October 13. in 12:25 (in app script there is way to convert data input into needed format)Birthplace BelgradeBaptism_date 1981. October 13.Baptism_place Belgrade, Saint Petka's ChappelPerson_name IvanPerson_sex MaleParent ...etc...I need T-SQL statement to put together multiple tables and fields into one table.Sincerely yours,Fr. ivanSerbiaMon, 13 Aug 2012 12:43:54 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxHow about this?[code="sql"]IF OBJECT_ID('dbo.RECORDS') IS NOT NULL DROP TABLE dbo.RECORDS;IF OBJECT_ID('dbo.NAMES') IS NOT NULL DROP TABLE dbo.NAMES;IF OBJECT_ID('dbo.DATES') IS NOT NULL DROP TABLE dbo.DATES;IF OBJECT_ID('dbo.PARENTS') IS NOT NULL DROP TABLE dbo.PARENTS;IF OBJECT_ID('dbo.BAPTISM') IS NOT NULL DROP TABLE dbo.BAPTISM;--Sample data in tables are, as followsCREATE TABLE dbo.RECORDS(ID int,book int,page int,number int);insert into dbo.RECORDS values ( 1, 1, 1, 1 );insert into dbo.RECORDS values ( 2, 1, 1, 2 );insert into dbo.RECORDS values ( 3, 1, 1, 3 );insert into dbo.RECORDS values ( 4, 1, 1, 4 );insert into dbo.RECORDS values ( 5, 1, 1, 5 );insert into dbo.RECORDS values ( 6, 1, 1, 6 );insert into dbo.RECORDS values ( 7, 1, 1, 7 );insert into dbo.RECORDS values ( 8, 1, 1, 8 );insert into dbo.RECORDS values ( 9, 1, 1, 9 );insert into dbo.RECORDS values ( 10, 1, 1, 10 );/*TABLE RECORDSID book page number1 1 1 11 1 1 21 1 1 3etc. (I have ten records per each page)*/CREATE TABLE dbo.NAMES(ID int,person_name varchar(50),person_sex char(6),priest_name varchar(50),priest_title varchar(30),godfather_name varchar(50));insert into dbo.NAMES values ( 1, 'Lara', 'female', 'Alexandar Pesic', 'priest', 'Goran Jevtic' );insert into dbo.NAMES values ( 2, 'Goran', 'male', 'Dusan Kovac', 'archpriest', 'Milena Taylor' );/*TABLE NAMESID person_name person_sex priest_name priest_title godfather_name1 Lara female priest Alexandar Pesic priest Goran Jevtic2 Goran male Dusan Kovac archpriest Milena Tayloretc.*/CREATE TABLE dbo.DATES(ID int,birth_date varchar(30),baptism_date varchar(30));insert into dbo.DATES VALUES ( 1, '1950., July 13.', '1952., November 14.' );/*TABLE DATESID birth_date baptism_date1 13.07.1950 (in format YYYY., MMMM DD. e.t. 1950., July 13.) 14.11.1952 (in format 1952., November 14.)etc.*/CREATE TABLE dbo.PARENTS(ID int,father_first_name varchar(30),father_last_name varchar(30),father_nationality varchar(30),father_faith varchar(30),mother_name varchar(30),mother_birthlast_name varchar(30));insert into dbo.PARENTS values ( 1, 'Goran', 'Pesic', 'Serbian', 'Orthodox', 'Bosa', 'Delic' );/*TABLE PARENTSID father_first_name father_last_name father_nationality father_faith mother_name mother_birthlast_name...1 Goran Pesic Serbian Orthodox Bosa Delicetc.My Result data should be, as excepted in application:TABLE BAPTISMID book page number birth_date baptism_date person_name parent priest godfather1 1 1 1 1950., July 13. 1952., November 14. Lara, female Goran Pesic and Bosa b. Delic, Serbian Orthodox...I started my app for research over data. The reason for denormalizing is that I received application for printing our data in unique paper form, and that app use only one table, with all the data in it.*/CREATE TABLE dbo.BAPTISM(ID int,book int,page int,number int,birth_date varchar(30),baptism_date varchar(30),person_name varchar(100),parent varchar(100),priest varchar(100),godfather varchar(100),);insert into dbo.BAPTISM ( ID, book, page, number, birth_date, baptism_date, person_name, parent, priest, godfather )select REC.ID, REC.book, REC.page, REC.number, DAT.birth_date, DAT.baptism_date, person_name = PAR.father_first_name + '' + PAR.father_last_name, parent = 'father', priest = NAM.priest_name, godfather = NAM.godfather_namefrom dbo.RECORDS REC INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;select * from dbo.BAPTISM;[/code]I'm not sure where the 'people' column comes from...If you prefer, you can create the BAPTISM table without declaring it like this:[code="sql"]select REC.ID, REC.book, REC.page, REC.number, DAT.birth_date, DAT.baptism_date, person_name = PAR.father_first_name + '' + PAR.father_last_name, parent = 'father', priest = NAM.priest_name, godfather = NAM.godfather_nameinto dbo.BAPTISMfrom dbo.RECORDS REC INNER JOIN dbo.NAMES NAM ON NAM.ID = REC.ID INNER JOIN dbo.DATES DAT ON DAT.ID = REC.ID INNER JOIN dbo.PARENTS PAR ON PAR.ID = REC.ID;[/code]Mon, 13 Aug 2012 08:50:55 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxI think I may now understand what you are trying to do....but there again I may well be completely wrong!You have a "printing application" that requires a table as its record source.In order to get the details for the print you need to provide some data and which book/page to print it on...each page has 10 records.so...I think your TABLE_RECORDS should look like thie[code="other"]ID book page number1 1 1 1 2 1 1 23 1 1 34 1 1 45 1 1 56 1 1 67 1 1 78 1 1 89 1 1 9 10 1 1 1011 1 2 112 1 2 2 13 1 2 3[/code]etceg...records 11 to 20 will print on page twp...and so onfor some reason you have split your data into separate tables...and now wish to join it back together by ID..is this correct?Have you still got the original table imported from MS Access? ...becasue I have a feeling that may already have all the details you need...ie before you split it out.You may need some help in concatenating (joining) columns and formatting dates to meet your print requirements, but before we go there...is this a correct interpretation of your problem?Mon, 13 Aug 2012 08:15:11 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThanks for the answer.Sample data in tables are, as followsTABLE RECORDSID book page number1 1 1 11 1 1 21 1 1 3etc. (I have ten records per each page)TABLE NAMESID person_name person_sex priest_name priest_title godfather_name1 Lara female priest Alexandar Pesic priest Goran Jevtic2 Goran male Dusan Kovac archpriest Milena Tayloretc.TABLE DATESID birth_date baptism_date1 13.07.1950 (in format YYYY., MMMM DD. e.t. 1950., July 13.) 14.11.1952 (in format 1952., November 14.)etc.TABLE PARENTSID father_first_name father_last_name father_nationality father_faith mother_name mother_birthlast_name...1 Goran Pesic Serbian Orthodox Bosa Delicetc.My Result data should be, as excepted in application:TABLE BAPTISMID book page number birth_date baptism_date person_name parent priest godfather1 1 1 1 1950., July 13. 1952., November 14. Lara, female Goran Pesic and Bosa b. Delic, Serbian Orthodox...I started my app for research over data. The reason for denormalizing is that I received application for printing our data in unique paper form, and that app use only one table, with all the data in it.Sincerely yoursFr. Ivan DelicSerbiaSun, 12 Aug 2012 10:12:39 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]priest Ivan Delic (8/9/2012)[/b][hr]Hi all!I have a simple question. I've made a simple database in Access and upsized it in my SQL Server 2005 Express edition Service Pack 4 on my Windows 7 machine, 2GB RAM.That database was newbie one, I tried to normalized my data in about 10 tables. In one table called Records I put these columnsID int NOT NULL PK auto-increment (I don't recall at this moment how is it called actually)book int NULLpage int NULLnumber int NULLSecond table is called names with following structure:ID int NOT NULL PK auto-increment (see above)name1 varchar (70)name2 varchar (120)Third table is most complex one. Now I realize that I should make it simple. Table name is Parents and the structure is:ID int NOT NULL PK auto-increment (see above)father_first_name varchar (50)father_last_name varchar (50)father_occupation varchar (50)...All those ID fields are the same, e. t. have the same value, so I want to make one table with all those fields together. I know of routine such asSELECT father_first_name + '' + father_last_name + '' + father occupation AS father,which is, in my experience with SQL Server, the best solution for putting few fields together in one field.Could anyone help me with my problem.P. S. I am aware of possible solutionINSERT INTO...ASSELECTFROMbut I don't know the whole T-SQL sequence.Sincerely yours,Fr. Ivan DelicSerbia[/quote]First, I wuldn't denormaize like that. Second, I don't believe you could anyway because there is absolutely nothing in those tables to relate them to each other unless you're 100% sure that the ID columns are, in fact, currently 1:1:1.Sun, 12 Aug 2012 07:05:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxHellocould you please provide some sample data for each of your tables and also what results you are expecting, based on the sample data, once they are all put into one table?Phil suggested reading a link in how to provide this in his post above.If you are still unclear...pls post back, I am sure we can help, but some idea of the data that you can see would help us help you.hope this helps....look forward to your reply.Sat, 11 Aug 2012 16:03:43 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThanks again.Table1 is called Records, Table2 Names, Table3 Parents. They could be related by ID field which has the same value for all of them.I was recently asked to create only one table and put all of my data in that one table. So how to insert data from other tables in one?I hope my answer is clear.Sincerely yours,Fr. Ivan DelicSerbiaSat, 11 Aug 2012 13:50:45 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]priest Ivan Delic (8/10/2012)[/b][hr]Thanks for the quick answer.I simply want to copy my data from table2, table3 to table1.I have about 25,000 records.Fr. Ivan Delic[/quote]Please follow the link in my signature to find out how to post questions in such a way as to make them easy to understand and answer.Fri, 10 Aug 2012 08:29:05 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspx[quote][b]priest Ivan Delic (8/10/2012)[/b][hr]Thanks for the quick answer.I simply want to copy my data from table2, table3 to table1.I have about 25,000 records.Fr. Ivan Delic[/quote]what are tables 1,2,3?what are the columns in each table?and how do the columns from table 1/2 relate to table3?Fri, 10 Aug 2012 08:22:01 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxThanks for the quick answer.I simply want to copy my data from table2, table3 to table1.I have about 25,000 records.Fr. Ivan DelicFri, 10 Aug 2012 07:52:24 GMTpriest Ivan Delichttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxI could not get what you really want but will complete correct sequence for select into..Insert into NewTableName(ColumnName1,ColumnName2)Select ColumnName1,ColumnName2+ColumnName3 from OldTableNameIf you want Concat of column2 & 3 to be inserted into column2 of new table.Fri, 10 Aug 2012 05:57:53 GMTrahul.rahuzzhttp://www.sqlservercentral.com/Forums/Topic1342810-338-1.aspxHi all!I have a simple question. I've made a simple database in Access and upsized it in my SQL Server 2005 Express edition Service Pack 4 on my Windows 7 machine, 2GB RAM.That database was newbie one, I tried to normalized my data in about 10 tables. In one table called Records I put these columnsID int NOT NULL PK auto-increment (I don't recall at this moment how is it called actually)book int NULLpage int NULLnumber int NULLSecond table is called names with following structure:ID int NOT NULL PK auto-increment (see above)name1 varchar (70)name2 varchar (120)Third table is most complex one. Now I realize that I should make it simple. Table name is Parents and the structure is:ID int NOT NULL PK auto-increment (see above)father_first_name varchar (50)father_last_name varchar (50)father_occupation varchar (50)...All those ID fields are the same, e. t. have the same value, so I want to make one table with all those fields together. I know of routine such asSELECT father_first_name + '' + father_last_name + '' + father occupation AS father,which is, in my experience with SQL Server, the best solution for putting few fields together in one field.Could anyone help me with my problem.P. S. I am aware of possible solutionINSERT INTO...ASSELECTFROMbut I don't know the whole T-SQL sequence.Sincerely yours,Fr. Ivan DelicSerbiaThu, 09 Aug 2012 10:10:12 GMTpriest Ivan Delic