Link to home
Start Free TrialLog in
Avatar of greenrc
greenrc

asked on

for xml auto qand nested inner joins

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>
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
Avatar of greenrc
greenrc

ASKER

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>
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  
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20269410
DTS package Date: 02/07/2002 07:23AM PST  
https://www.experts-exchange.com/jsp/qShow.jsp?ta=mssql&qid=20264312
port VBscript Encrypt function to javascript Date: 02/11/2002 11:04PM PST  
https://www.experts-exchange.com/jsp/qShow.jsp?ta=javascript&qid=20265865
auto detection of email client Date: 09/09/2001 10:17PM PST  
https://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20180741

Thanks,
Anthony
Avatar of greenrc

ASKER

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>
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.
Avatar of greenrc

ASKER

please refund
ASKER CERTIFIED SOLUTION
Avatar of modulo
modulo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial