What I'm trying to do is put together a query where I do something like this:SELECT DISTINCT TOP 25 tblOne.colOne, tblOne.colTwo, tblOne.colThree, tblTwo.colFour, --tblSix.xmlStuffFROM tblOne LEFT JOIN tblThree ON tblOne.colOne = tblThree.colOne LEFT JOIN (SELECT colOne, SUM(colFour) as colFour FROM tblFour GROUP BY (colOne)) AS tblTwo ON tblTwo.colOne = tblOne.colOne --LEFT JOIN (SELECT (SELECT colOne, colFive, colFour FROM tblFive for xml auto, type, elements) as xmlStuff) as tblSix on tblOne.colOne = tblOne.colOneWHERE tblOne.colTwo like '123-%'
Sorry for the lame names, but this is for work. Basically, my problem is the third left join. tblFive has three columns: a primary key (can't use it for this), a foreign key ("colOne"), and some data. Their are many records in the table that share a common colOne but have different data. What I'd like to do is have that last join combine the various data values that correspond to a given colOne value into an xml field that is returned as part of my data set. That way, I'd end up with a set of records for which colOne is distinct, each having the previously mentioned xml field of combined values for use elsewhere.Some issues: - Can't apply distinct to this thing if I want to return as part of my dataset an xml field. - Can't figure a way to extract distinct colOne values from tblFive while using those distinct values at the same time to build my xml field.The query above is what I have so far. Am I going about this all wrong? I'd love any insight anyone could provide in this matter. Thanks!
Need help combining fields as part of sql join
Posted - 2008-06-25 : 16:03:21
