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>
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>
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>
<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
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
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>
<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.
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.
ASKER
please refund
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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