>>Hi,
>>
>>A few days ago I requested help to make a sql select statement combining five tables. I received a workable and correct answer and also the remark my database design was not correct, I should combine three tables into one. I am convinced and have changed the design. However now again I have to ask for help to create the cursor.
>>
>>CREATE CURSOR curNames (ID i, fullname c(10))
>>INSERT INTO curNames values ( 1, "Koen")
>>INSERT INTO curNames VALUES ( 2, "John")
>>INSERT INTO curNames VALUES ( 3, "Pete")
>>INSERT INTO curNames VALUES ( 4, "Donald")
>>INSERT INTO curNames VALUES ( 5, "Bill")
>>
>>CREATE CURSOR curEmailcontent (Id i, iname i, content m, sendstatus i,iEmailmngr i)
>>** sendstatus 1 = sender, 2, = receiver, 3 = cc, 4 = bcc
>>INSERT INTO curEmailcontent VALUES( 1, 2, "The first",1,1)
>>INSERT INTO curEmailcontent values( 2, 1, "This is an Email", 1, 2)
>>INSERT INTO curEmailcontent values( 3, 3, "This is an Email", 2, 2)
>>INSERT INTO curEmailcontent values( 4, 4, "This is an Email", 2, 2)
>>INSERT INTO curEmailcontent values( 5, 5, "This is an Email", 3, 2)
>>INSERT INTO curEmailcontent values( 6, 2, "This is an Email", 4, 2)
>>
>>
>>CREATE CURSOR curEmailmngr (id i)
>>INSERT INTO curEmailmngr values ( 1)
>>INSERT INTO curEmailmngr values ( 2)
>>
>>*!* SELECT curNames
>>*!* BROWSE normal
>>*!* SELECT curEmailcontent
>>*!* BROWSE normal
>>*!* SELECT curEmailmngr
>>*!* BROWSE normal
>>
>>clea
>>TEXT TO lcText PRETEXT 1
>>
>>I would like to have for Where EmailID = 2
>>
>>fullnamefrom fullnameto fullnamecc fullnamebcc content
>>Koen This is an Email
>> Pete This is an Email
>> FDonald This is an Email
>> Bill This is an Email
>> John This is an Email
>>ENDTEXT
>>
>>? lcText
>>
>>return
>>
>>
>>
>>
>>Someone to assist me with this?
>>
>>Regards,
>>
>>Koen
>
>
>
>Select ;
> Cast( Iif( sendstatus = 1, N.FullName, '') As c(10)) As fullNameFrom, ;
> Cast( Iif( sendstatus = 2, N.FullName, '') As c(10)) As fullNameTo, ;
> Cast( Iif( sendstatus = 3, N.FullName, '') As c(10)) As fullNameCC, ;
> Cast( Iif( sendstatus = 4, N.FullName, '') As c(10)) As fullNameBCC, ;
> ec.Content ;
> from curEmailcontent ec ;
> inner Join curNames N On ec.iname = N.Id ;
> where ec.iEmailmngr = 2
>
>
>As I remember, it was me who suggested to change the structure but this wasn't what I meant. We, SQL developers, are too much into denormalizing the data with IDs. IMHO, that simply leads to unnecessary joins and makes to deal with data complex. What real benefit would you have if you have the Names and Email content separated like this? You must think of it. For example, wouldn't it be much more easy if it were:
>
>
>Create Cursor curEmailcontent (Id i, fullnamefrom m, fullnameto m, fullnamecc m, fullnamebcc m, content m)
>Insert Into curEmailcontent Values( 1, 'John', '', '', '', "The first")
>Insert Into curEmailcontent Values( 2, 'Koen', 'Pete,Donald', 'Bill', 'John', "This is an Email")
>
>
>Or even:
>
>Create Cursor curEmailcontent (Id i, Detail m)
>Insert into curEmailcontent values (1, '<email from="John" to="" cc="" bcc="" content="The first"/>')
>Insert into curEmailcontent values (2, '<email from="Koen" to="Pete,Donald" cc="Bill" bcc="John" content="This is an Email"/>')
>
>
>Both of these are easily parsable (if there is a need to),
>
>Of course I am just suggesting these without knowing you real use cases.
Cetin,
You are correct it was upon your suggestion I changed my Database design, now with you secondhelp on the construction of the query I will stick to this design, the query works like a charm and if I should change the table Emailcontents as one of your suggestions I should change lots of other queries also, the design as is now works fine for me.
Thanks again for your help and suggestions..
Koen
