I will make the assumption that you didn't actually read the article that was suggested. If you had you would have realized that we want is create table statements and insert statements to those tables.

Something like this.

create table #DEMOGRAPHICS

(

IMREDEM_CODE int,

DEM_EXTERNALID varchar(10),

DEM_LASTNAME varchar(10),

DEM_FIRSTNAME varchar(10),

DEM_DATEOFBIRTH datetime

)

create table #CHARTATTACHMENT

(

IMREDEMEC_CODE int,

IMRENOTE_CODE int,

CHAT_DATE datetime

)

create table #NOTES_TEXTDATA

(

IMRENOTE_CODE int,

NOTE_TEXT text

)

insert #DEMOGRAPHICS

SELECT '2','4','LN4','FN4','Mar 18 1983 12:00AM' union all

SELECT '5','7','LN7','FN7','Feb 6 1996 12:00AM' union all

SELECT '8','10','LN8','FN8','Feb 15 1957 12:00AM' union all

SELECT '20','22','LN9','FN9','Jun 9 1971 12:00AM' union all

SELECT '25','27','LN10','FN10','Jul 22 1996 12:00AM' union all

SELECT '28','30','LN11','FN11','Apr 13 1993 12:00AM'

insert #CHARTATTACHMENT

SELECT '2','146669','Feb 3 2005 1:34PM' union all

SELECT '2','146720','Feb 3 2005 2:00PM' union all

SELECT '2','146955','Feb 3 2005 3:47PM' union all

SELECT '2','147394','Feb 4 2005 8:37AM' union all

SELECT '2','591108','Mar 8 2006 2:09PM' union all

SELECT '2','2589211','Mar 24 2011 12:37PM' union all

SELECT '5','863598','Oct 9 2006 12:47PM' union all

SELECT '5','2322272','Jun 17 2010 2:49PM' union all

SELECT '5','2781700','Oct 24 2011 4:56PM' union all

SELECT '7','104338','Dec 23 2004 11:28AM' union all

SELECT '8','667930','May 9 2006 2:13PM' union all

SELECT '8','879185','Oct 20 2006 8:41AM' union all

SELECT '8','2203832','Feb 17 2010 2:12PM' union all

SELECT '8','2322216','Jun 17 2010 2:29PM' union all

SELECT '20','1490261','Feb 19 2008 8:03PM' union all

SELECT '20','1783630','Jan 5 2009 11:03AM' union all

SELECT '20','2380446','Aug 18 2010 11:05AM' union all

SELECT '20','2490167','Dec 6 2010 1:21PM' union all

SELECT '25','120797','Jan 11 2005 7:19AM'

insert #NOTES_TEXTDATA

SELECT '146669','Text1' union all

SELECT '146720','Text2' union all

SELECT '146955','Text3' union all

SELECT '147394','Text4' union all

SELECT '591108','Text5' union all

SELECT '2589211','Text6' union all

SELECT '863598','Text7' union all

SELECT '2322272','Text8' union all

SELECT '2781700','Text9' union all

SELECT '104338','Text10' union all

SELECT '667930','Text11' union all

SELECT '879185','Text12' union all

SELECT '2203832','Text13' union all

SELECT '2322216','Text14' union all

SELECT '1490261','Text15' union all

SELECT '1783630','Text16' union all

SELECT '2380446','Text17' union all

SELECT '2490167','Text18' union all

SELECT '120797','Text19'

Now we both have the same temp tables that we are working with so we have a very solid common ground.

Let's take your query. You are including a predicate for IMREDEMEC_CODE. Since this is an int datatype you should not use strings in your query.

Given the nature of what you are returning you need to remove the XML portion so you can see the rows being returned.

--SELECT(

SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_LASTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT

FROM #DEMOGRAPHICS

INNER JOIN #CHARTATTACHMENT ON #DEMOGRAPHICS.IMREDEM_CODE = #CHARTATTACHMENT.IMREDEMEC_CODE

INNER JOIN #NOTES_TEXTDATA ON #CHARTATTACHMENT.IMRENOTE_CODE = #NOTES_TEXTDATA.IMRENOTE_CODE

WHERE IMREDEMEC_CODE BETWEEN 1 AND 10

AND DEM_LASTNAME <>'Demonstration'

AND DEM_LASTNAME <>'Test'

AND DEM_LASTNAME <>'Train'

AND DEM_LASTNAME <>'Erroneous'

--FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL

--) AS COL_XML;

That all seems to be working just fine to me. Can you explain what is not working correctly?

Of course let's not forget to include the cleanup to drop our temp tables.

drop table #DEMOGRAPHICS

drop table #CHARTATTACHMENT

drop table #NOTES_TEXTDATA