Solved

for xml auto qand nested inner joins

Posted on 2002-05-23
7
878 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now