>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

You can use the following query:

SELECT IIF(CurEmailContent.SendStatus = 1, CurNames.FullName, SPACE(10)) AS FullNameFrom, ;
		IIF(CurEmailContent.SendStatus = 2, CurNames.FullName, SPACE(10)) AS FullNameTo, ;
		IIF(CurEmailContent.SendStatus = 3, CurNames.FullName, SPACE(10)) AS FullNameCC, ;
		IIF(CurEmailContent.SendStatus = 4, CurNames.FullName, SPACE(10)) AS FullNameBCC, ;
		CurEmailContent.Content ;
	FROM CurEmailContent ;
	LEFT JOIN CurNames ON CurEmailContent.iName = CurNames.ID ;
	WHERE CurEmailContent.iEmailmngr = 2

Or alternatively a solution with a Type column:

SELECT CAST('FROM' AS C(10)) AS Type, ;
		CurNames.FullName AS FullName, ;
		CurEmailContent.Content ;
	FROM CurEmailContent ;
	LEFT JOIN CurNames ON CurEmailContent.iName = CurNames.ID ;
	WHERE CurEmailContent.SendStatus = 1 ;
	 AND CurEmailContent.iEmailmngr = 2 ;
UNION ALL ;
SELECT CAST('TO' AS C(10)) AS Type, ;
		CurNames.FullName AS FullName, ;
		CurEmailContent.Content ;
	FROM CurEmailContent ;
	LEFT JOIN CurNames ON CurEmailContent.iName = CurNames.ID ;
	WHERE CurEmailContent.SendStatus = 2 ;
	 AND CurEmailContent.iEmailmngr = 2 ;
UNION ALL ;
SELECT CAST('CC' AS C(10)) AS Type, ;
		CurNames.FullName AS FullName, ;
		CurEmailContent.Content ;
	FROM CurEmailContent ;
	LEFT JOIN CurNames ON CurEmailContent.iName = CurNames.ID ;
	WHERE CurEmailContent.SendStatus = 3 ;
	 AND CurEmailContent.iEmailmngr = 2 ;
UNION ALL ;
SELECT CAST('BCC' AS C(10)) AS Type, ;
		CurNames.FullName AS FullName, ;
		CurEmailContent.Content ;
	FROM CurEmailContent ;
	LEFT JOIN CurNames ON CurEmailContent.iName = CurNames.ID ;
	WHERE CurEmailContent.SendStatus = 4 ;
	 AND CurEmailContent.iEmailmngr = 2 ;
INTO CURSOR cuResult