问题描述
写这篇文章有点麻烦。如果你这么倾向并且有一个的时刻，我真的很感激你的洞察力。
i具有相当于采购订单类型的设置。 ..一个描述性标题
与订单项相关联。我有3个表...标题，lineitem和
辅助。辅助表保存有关非标准的线条项的信息，如subbordinate
。每个表格
都有一个uniqueId列。 lineitem和辅助表也有一个
parentUniqueId列。总是至少有一个lineitem
parentUniqueId引用标题'的uniqueId。有时间，当一个
lineitem会引用另一个lineitem'的UniqueId。补充数据在
中，辅助表以相同的方式引用lineitems，除了
没有反身的自我加入。我知道一张图片胜过千言万语，但我认为这很简单。
这些表中的数据以xml格式发送通过Web服务到另一个使用完全不同的平台的
服务器。 Web服务以xml格式发回每条记录的
状态，确认哪些标题，
和/或失败。我一直在尝试编写的查询应该只是简单地从所有3个表中返回数据，因为一个合并的记录集已过滤
这样所有相关的记录都会被返回一个给定的，与
相关的标题或lineitem或辅助，如果确认为失败。
我现在拥有的东西是：
选择项目。*，
a.uniqueId auxId
来自
（
选择h.uniqueId poId，
l.uniqueId lineId
from lineItems l
加入标题h
on h .uniqueId = l.parentUniqueId
union
选择h.uniqueId poId，
child.uniqueId lineId
from lineItems child
join lineItems parent
on child.parentUniqueId = pa rent.uniqueId
加入标题h
on h.uniqueId = parent.parentUniqueId
）项目
左加入辅助a
on a.parentUniqueId = items.lineId
这会生成* all *数据的正确视图。我需要帮助
标准，以便如果任何相关项目的uniqueId被称为已解散
那么其他相关项目中的任何一项都应返回记录集。
感谢您的时间，想法和建议。
我
我们可以不用图片。但是，我们非常喜欢的是：
o所涉及表格的CREATE TABLE脚本（最好简化
以涵盖问题中必不可少的内容）
o带有INSERT数据的示例语句。
o给出样本的预期结果。
至少有两个原因我们喜欢这个：
1）通过将脚本复制并粘贴到查询分析器中，可以很容易地开发出经过测试的解决方案。
2）它有助于澄清你叙述中的含糊之处。
从问题来看，似乎有一些状态栏
某处，但我可以找不到文字。 （你不愿意使用
移位键并没有真正帮助。）而且我不介意看一个
的例子如何一个lineitem可以引用不同类型的父项。
-
Erland Sommarskog，SQL Server MVP，
SQL Server SP3的联机书籍
having a spot of trouble writing this one. if you are so inclined and have a
moment, i''d really appreciate your insight.
i have what amounts to a purchase order type of setup...a descriptive header
associated with line items. i have 3 tables...header, lineitem, and
auxiliary. the auxiliary table holds information like subbordinate
information about lineitems that are "non-standard". each of these tables
has a uniqueId column. the lineitem and auxiliary tables have a
parentUniqueId column as well. there is always at least one lineitem whos
parentUniqueId references a header''s uniqueId. there are time though, when a
lineitem will reference another lineitem''s UniqueId. supplimental data in
the auxiliary table references lineitems in the same fashion except there is
no reflexive self-joining. i know a picture is worth a thousand words, but i
think this is pretty straight-forward.
the data in these tables is sent in xml format via web services to another
server that uses a completely different platform. the web service sends the
status of each record back in xml format confirming which headers,
lineitems, and auxiliary items (by uniqueId) were successfully transfered
and/or which failed. the query i have been trying to write should simply
return the data from all 3 tables as one consolidated record set filtered
such that all related records are returned as long as neither a given,
related header or lineitem or auxiliary where confirmed as failed.
the meat of what i have now is something like:
select items.* ,
a.uniqueId auxId
from
(
select h.uniqueId poId ,
l.uniqueId lineId
from lineItems l
join header h
on h.uniqueId = l.parentUniqueId
union
select h.uniqueId poId ,
child.uniqueId lineId
from lineItems child
join lineItems parent
on child.parentUniqueId = parent.uniqueId
join header h
on h.uniqueId = parent.parentUniqueId
) items
left join auxiliary a
on a.parentUniqueId = items.lineId
this generates the correct view of *all* the data. i need help with the
criterion so that if any related item''s uniqueId was said to be "defunct"
then NONE of the other related items should return in the recordset.
thanks for your time, thoughts, and advice.
me
We can do without the pictures. However, what we are very fond of are:
o CREATE TABLE scripts for the involved tables (preferrably simplified
to cover the essential in the problem)
o Sample statements with INSERT data.
o The desired result given that sample.
There are at least two reasons we like this:
1) By copying-and-pasting the scripts into Query Analyzer, it''s easy to
develop a tested solution.
2) It helps to clarify ambiguities in your narrative.
Judging from the problem it appears that there is some status column
somewhere, but I could not find it the text. (Your reluctance to use
the shift key does not really help.) And I wouldn''t mind to see an
example how a lineitem can refer to different sort of parent items.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
这篇关于查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！