?
Solved

for xml auto qand nested inner joins

Posted on 2002-05-23
7
Medium Priority
?
884 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

765 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