sql server using outer join result into table format

Question

I am setting a query to send the results via email and I am getting the result formatting incorrect.

I want to select values from two tables where in table 1 a certain field is null and also add a related field from table 2 where table 1 id = table 2 id. But also include fields in table 1 that may not be in table 2 but has the certain field null.

 DECLARE @TITLE NVARCHAR(MAX)
 DECLARE @BODY NVARCHAR(MAX)

 SET @TITLE =CAST(( SELECT DISTINCT A.CUSTKEY as 'td','',
 B.CUSTPROJ as 'td','',   A.ITEMS AS 'td','', A.SOLDDATE as 'td','',
 A.LASTORDEREDDATE as 'td','', A.OBJID as 'td'
 FROM PRODUCTDATA AS A LEFT OUTER JOIN CLIENT_TABLE as B
 on A.CUSTkey = B.CUSTkey 
 WHERE LASTORDEREDDATE IS NULL GROUP BY A.CUSTKEY,B.CUSTPROJ, 
 A.ITEMS, A.SOLDDATE, A.OBJID,A.LASTORDEREDDATE
 FOR XML PATH ('tr'), ELEMENTS) AS NVARCHAR(MAX))

 SET @BODY = '<html><body><H4>NEW CLIENT PURCHASES</H4>
 <table border = 2>
 <tr>
 <th> CUST KEY </th> <th> CLIENT NAME </th> <th> NEW PURCHASES </th>
 <th> SOLD DATE </th> <th> ORDERED DATE </th> <th> PRODUCT ID </th></tr>'

 SET @BODY = @BODY + @TITLE + '</TABLE></BODY></HTML>'

EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Admin',
@body =   @BODY,
@body_format = 'HTML';

But it appears as follows:

NEW CLIENT PURCHASES
CUSTkEY CLIENT NAME NEW PURCHASES    SOLD DATE      ORDERED DATE PRODUCT ID 
9189ad  PHOEBE       LDS              20130107T12:15:00    NULL         BGD01 
8765ad  MONICA       PANTS            20130107T12:15:00    NULL         BGD01 
3421ad  JOEY         BAG              20130107T12:15:00    NULL         BGD01 
2354ad  RACHEL       HAT              20130107T12:15:00    NULL         BGD01   
7653ef  SHOES     20121213T06:15:00                                     hfd00

As you can see the last row happens to be a row in table 1 where the client does not exist in the client table 2 and thus the row should look like this:

  CUSTkEY   CLIENT NAME NEW PURCHASES    SOLD DATE       ORDERED DATE    PRODUCT ID 

   7653ef                  SHOES       20121213T06:15:00   NULL             hfd00

Try to use `XSINIL` in query like `FOR XML PATH ('tr'), ELEMENTS XSINIL`. Maybe it will allow to keep placeholders for empty values — Mihail Golubev, Jan 08 '13 at 21:58

score 1 · Answer 1 · answered Jan 08 '13 at 22:19

1

To include empty tags when you have several columns with the same name, you have to wrap each column into its own XML generator like this:

SELECT 
 (SELECT (SELECT n1) FOR XML PATH('td'),TYPE),
 (SELECT (SELECT n2) FOR XML PATH('td'),TYPE), 
 (SELECT (SELECT n3) FOR XML PATH('td'),TYPE)
FROM (VALUES(NULL,NULL,1))x(n1,n2,n3)
FOR XML PATH('tr'), ELEMENTS

answered Jan 08 '13 at 22:19

Sebastian Meine

11,260
29
41

I dont understand the from line 'FROM (VALUES(NULL,NULL,1))x(n1,n2,n3)' FROM PRODUCTDATA AS A LEFT OUTER JOIN CLIENT_TABLE as B on A.CUSTkey = B.CUSTkey WHERE LASTORDEREDDATE IS NULL GROUP BY A.CUSTKEY,B.CUSTPROJ, A.ITEMS, A.SOLDDATE, A.OBJID,A.LASTORDEREDDATE FOR XML PATH ('tr'), ELEMENTS) AS NVARCHAR(MAX)) – vbNewbie Jan 08 '13 at 22:38

sql server using outer join result into table format

1 Answers1