Solved

for xml auto qand nested inner joins

Posted on 2002-05-23
7
880 Views
Last Modified: 2009-12-16
I am having some trouble with nested inner joins

Here is the query that doesn't work properly:

SELECT    
  maintable.*,
  subtable1.*,
  subtable2.*
FROM
  maintable
INNER JOIN
  subtable1 ON maintable.mainid = subtable1.mainID
INNER JOIN
  subtable2 ON maintable.mainid = subtable2.mainID
WHERE
 maintable.id = 15
FOR XML AUTO

The current results are:

<maintable mainid="15">
  <subtable1 subid="91" mainID="15">
    <subtable2 subid="104" mainID="15" />
  </subtable1>
  <subtable1 subid="92" mainID="15">
    <subtable2 subid="162" mainID="15" />
  </subtable1>
</maintable>

My desired results are:

<maintable mainid="15">
  <subtable1 subid="91" mainID="15" />
  <subtable1 subid="92" mainID="15" />
  <subtable2 subid="104" mainID="15" />
  <subtable2 subid="162" mainID="15" />
</maintable>
0
Comment
Question by:greenrc
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7031288
Try this:

SELECT    
 *
FROM
 maintable
INNER JOIN (
SELECT 1 as sub, * FROM subtable1
UNION ALL
SELECT 2 as sub * FROM subtable2
) as subtable
ON maintable.mainid = subtable.mainID
WHERE
maintable.id = 15
FOR XML AUTO


should give something like this:

<maintable mainid="15">
 <subtable sub="1" subid="91" mainID="15" />
 <subtable sub="1" subid="92" mainID="15" />
 <subtable sub="2" subid="104" mainID="15" />
 <subtable sub="2" subid="162" mainID="15" />
</maintable>

Hope this helps
0
 

Author Comment

by:greenrc
ID: 7031297
really need it like this:

<maintable mainid="15">
<subtable1 subid="91" mainID="15" />
<subtable1 subid="92" mainID="15" />
<subtable2 subid="104" mainID="15" />
<subtable2 subid="162" mainID="15" />
</maintable>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7033547
First of all, since subids are included in the maintable there is no need for the Inner Joins.  But I suspect you are attempting to expand the XML once you get the correct format.

If you can use For XML Explicit instead of For XML Auto, I can post the query.

In the meantime you may also, want to maintain your open questions:

DTS Import Date: 02/21/2002 03:26AM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20269410
DTS package Date: 02/07/2002 07:23AM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20264312
port VBscript Encrypt function to javascript Date: 02/11/2002 11:04PM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20265865
auto detection of email client Date: 09/09/2001 10:17PM PST  
http://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20180741

Thanks,
Anthony
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:greenrc
ID: 7035233
I have achieved this output via XML Explicit ... but am interestedin getting the following output using XML Auto .. if possible

<maintable mainid="15">
<subtable1 subid="91" mainID="15" />
<subtable1 subid="92" mainID="15" />
<subtable2 subid="104" mainID="15" />
<subtable2 subid="162" mainID="15" />
</maintable>
0
 

Expert Comment

by:CleanupPing
ID: 9280456
greenrc:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 

Author Comment

by:greenrc
ID: 9284136
please refund
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 9620423
PAQ'd and points refunded

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question