[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

for xml auto qand nested inner joins

Posted on 2002-05-23
7
Medium Priority
?
886 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 143

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

873 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