query help

[]

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

[Erland Sommarskog]

 ([e-mail]) writes:

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.

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

[]

sorry, ejo...

let's not trifle over the tables save that the all tables have a varchar(50)
column called uniqueId. the lineitem and auxiliary tables have an additional
varchar(50) column called parentUniqueId. let's say the header table's data
looks like:

header (uniqueId is fk to lineitem.parentUniqueId):

uniqueId
A

the lineitem table's data looks like:

lineitem (uniqueId is self-joined by parentUniqueId or an fk to auxiliary):

uniqueId parentUniqueId
X A
Y X
Z X

the aux table's data looks like:

auxiliary:

uniqueId parentUniqueId
L Y
M Z

the query from the previous post would return:

poId lineId auxId
A X
A Y L
A Z M

i'm trying to get only those related items where neither poId, lineId, nor
auxId are in a list of supplied values. i.e. if the list was "QRS", then the
above records *should* show. if the list was "L" for example, then none of
the data above should show...since L is either a poId, lineId, or auxId -
which is the actual case here.

[Greg D. Moore \(Strider\)]

"" <[e-mail]> wrote in message news:ti*******************@fe07.lga...

sorry, ejo...

let's not trifle over the tables save that the all tables have a varchar(50) column called uniqueId. the lineitem and auxiliary tables have an additional varchar(50) column called parentUniqueId. let's say the header table's data looks like:
Let's not "say the header table's data looks like" anything.

Erland's point about providing a real DDL is that folks here can then
recreate your exact configuration, design queries and test them against your
exact schema, not their best guess of your interpretation.

If you have business reasons (as is often the case) for needing to keep
details of your schema secret, break the DDL down to the bare minimum that
accurate describes the problm you're trying to solve.


header (uniqueId is fk to lineitem.parentUniqueId):

uniqueId
A

the lineitem table's data looks like:

lineitem (uniqueId is self-joined by parentUniqueId or an fk to auxiliary):
uniqueId parentUniqueId
X A
Y X
Z X

the aux table's data looks like:

auxiliary:

uniqueId parentUniqueId
L Y
M Z

the query from the previous post would return:

poId lineId auxId
A X
A Y L
A Z M

i'm trying to get only those related items where neither poId, lineId, nor
auxId are in a list of supplied values. i.e. if the list was "QRS", then the above records *should* show. if the list was "L" for example, then none of
the data above should show...since L is either a poId, lineId, or auxId -
which is the actual case here.

[]

| If you have business reasons (as is often the case) for needing to keep
| details of your schema secret, break the DDL down to the bare minimum that
| accurate describes the problm you're trying to solve.

jesus christ! how much more "bare minimum" does this get:

"all tables have a varchar(50) column called uniqueId. the lineitem and
auxiliary tables have an additional varchar(50) column called
parentUniqueId...i'm trying to get only those related items where neither
poId, lineId, nor auxId are in a list of supplied values."

there is *no* guessing involved. not only have i provided the structure for
each table, their relation to eachother, a sample of what their data looks
like, but i've even written 90% of the goddamn query...i need help with the
criterion! can no one do that?!!

[Hugo Kornelis]

On Wed, 17 Aug 2005 08:07:30 -0500,  wrote:

| If you have business reasons (as is often the case) for needing to keep
| details of your schema secret, break the DDL down to the bare minimum that
| accurate describes the problm you're trying to solve.

jesus christ! how much more "bare minimum" does this get:

"all tables have a varchar(50) column called uniqueId. the lineitem and
auxiliary tables have an additional varchar(50) column called
parentUniqueId...i'm trying to get only those related items where neither
poId, lineId, nor auxId are in a list of supplied values."

there is *no* guessing involved. not only have i provided the structure for
each table, their relation to eachother, a sample of what their data looks
like, but i've even written 90% of the goddamn query...i need help with the
criterion! can no one do that?!!

Hi ,

As Erland already pointed out in his first reply:

"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."

I fully agree with Erland - if you post the requested information, in
the requested form, then I'm quite sure that people can help you. But if
you decline to provide the information in the recommended form, then you
are indeed making it impossible for us to help you.

Some tips for assembling the CREATE TABLE and INSERT statements we need
to help you can be found here: www.aspfaq.com/5006.

And one other suggestion - don't use swear words. Some people are
offended by language like that. And if you are seeking help, offending
those who offer to help for free is generally not a good idea.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

 ([e-mail]) writes:

jesus christ! how much more "bare minimum" does this get:

"all tables have a varchar(50) column called uniqueId. the lineitem and
auxiliary tables have an additional varchar(50) column called
parentUniqueId...i'm trying to get only those related items where neither
poId, lineId, nor auxId are in a list of supplied values."

there is *no* guessing involved. not only have i provided the structure
for each table, their relation to eachother, a sample of what their data
looks like, but i've even written 90% of the goddamn query...i need help
with the criterion! can no one do that?!!

I read your previous post, but I failed to comprehend:

i'm trying to get only those related items where neither poId, lineId,
nor auxId are in a list of supplied values. i.e. if the list was "QRS",
then the above records *should* show. if the list was "L" for example,
then none of the data above should show...since L is either a poId,
lineId, or auxId - which is the actual case here.

It may be obvious to you, but it is not to me why none of the rows
would be selected when L is in the list.

You may know your business rules and business domain - we don't. And
if you fail to explain me, it's getting difficult to write a query
for you. I mean, you know the business rules, but can still not write
the query.

Anyway, it's all up to you. If you prefer to yell "Jesus Christ",
rather than helping us to help you, please do so.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[]

| Hi ,

hi, hugo.

| "what we are very fond of are:

well aware by now, i assure you.

| I fully agree with Erland - if you post the requested information, in
| the requested form, then I'm quite sure that people can help you. But if
| you decline to provide the information in the recommended form, then you
| are indeed making it impossible for us to help you.

usually, i agree too. it seems though, (some) people here are incapable of
providing assistance if the post is in *any other form*. i have a problem
with that. there was/is no need to jump through the hoops on this one, as
the most "complex" tables had 2 varchar columns...the other, 1. they related
to eachother in a parent/child,one/many fashion. are you telling me that
that is "hard" to grasp?

far, far from impossible...unless your only modus-operandi is
cut-copy-paste. in that case, i give my condolensces to those with
less-than-normal iqs. that may or may not apply to any of the responders in
this thread...i'm just saying, this was WAY to easy but made WAY too
complex...especially for sooo much bitching.

| Some tips for assembling the CREATE TABLE and INSERT statements we need
| to help you can be found here: www.aspfaq.com/5006.

i'm very...let me say, VERY...experienced with plsql, tsql, and ansi
sql...but, thanks for the link just the same.

| And one other suggestion - don't use swear words. Some people are
| offended by language like that. And if you are seeking help, offending
| those who offer to help for free is generally not a good idea.

i use the words i use. those of which it offends can simply foad (more
sarcastically than actually...pun intended ;^) i'll keep that in mind,
however it seems that unless i fill out the *official help request form*
there is no way i'll be getting help anyway.

i appreciate your input and suggestions, hugo. as it is, i've fixed the
query and have moved on to better things.

[]

| I read your previous post, but I failed to comprehend:

| It may be obvious to you, but it is not to me why none of the rows
| would be selected when L is in the list.
|
| You may know your business rules and business domain - we don't. And
| if you fail to explain me, it's getting difficult to write a query
| for you. I mean, you know the business rules, but can still not write
| the query.

understood. i do. and, i have.

| Anyway, it's all up to you. If you prefer to yell "Jesus Christ",
| rather than helping us to help you, please do so.

it is, and i've taken care of it. "jesus christ" was frustration and was not
in leu of me helping you help me. as a matter of fact, in each post i've
tried to get everyone to understand what i was going for...but all i got in
return was essentially, "we can't function till we can cut/copy/paste". the
table structure was NOT the issue at all and is NOT difficult to create on
your own...2 columns at most, for CHRIST'S sake. ;^) oh well, no need making
this casm of "official posting format" and "pragmatism" what you want...i
already understand that for most here (at least in this case), the divide is
substancially wide.

anyway, the query runs fine now.

[Erland Sommarskog]

 ([e-mail]) writes:

the table structure was NOT the issue at all and is NOT difficult to
create on your own...2 columns at most,
Had the rest of the problem description been clear enough I would maybe
have done it. Or just posted an untested query. But when you don't provide
tables, don't provide INSERT statements, don't provide sample data, and
your description is not clear enough, then the bar is too high for me
being interested in playing guessing games.

You see, it's not that I'm paid to do this or anything.
anyway, the query runs fine now.

Glad to hear that you got it working.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[]

| > the table structure was NOT the issue at all and is NOT difficult to
| > create on your own...2 columns at most,
|
| Had the rest of the problem description been clear enough I would maybe
| have done it. Or just posted an untested query. But when you don't provide
| tables, don't provide INSERT statements, don't provide sample data, and
| your description is not clear enough, then the bar is too high for me
| being interested in playing guessing games.

now we enter the realm of "horse-shit". i provided a description of the
tables, just not a create table script. not only that, but i also provided
sample data *and* sample output from the query *i* provided - the response i
got to both of these was: "Let's not 'say the header table's data looks
like' anything." in light of those 2 provisions, the bar was fairly low in
my average estimation...but, i'll give you the benefit-of-the-doubt and
assume you just didn't see my second post *or* the response it got.

each time i posted, i tried to clear up what my aim with the query was but
this was *never* addressed - as each respondant could not get over the
"official post format" condundrum/mantra. i would have tried to better
define this if someone/anyone would have even come close to bucking the
mantra...as no one could, i did not say any more than the first 2 posts
allowed.

| You see, it's not that I'm paid to do this or anything.

cry me a river. i'm a "non-paid" contributer to 3 other newsgroups in which,
my technical prowess is appreciated and respected...even moreso when the op
was vague with defining the problem. i do my best to help each one and i
only get pissed - or become less-than-useful - if the question posted
doesn't relate to the context of the ng. i'm pretty flexible when it comes
to cross and multi posting. this is what i had inadvertantly expected of
contributers here. as this was my first visit here, i will know what to
expect in the future in the way of help...should i ever feel like coming
back! (to which everyone is probably saying, "yeah"...that's fine by me!)

| > anyway, the query runs fine now.
|
| Glad to hear that you got it working.

i do appreciate everyone's participation...i just wish it would have been
more fruitful for all involved.

thanks anyway,

me

[Eric J. Holtman]

"" <[e-mail]> wrote in news:6o******************@fe05.lga:

it is, and i've taken care of it. "jesus christ" was frustration and
was not in leu of me helping you help me. as a matter of fact, in each
post i've tried to get everyone to understand what i was going
for...but all i got in return was essentially, "we can't function till
we can cut/copy/paste". the table structure was NOT the issue at all
and is NOT difficult to create on your own...2 columns at most, for
CHRIST'S sake. ;^) oh well, no need making this casm of "official
posting format" and "pragmatism" what you want...i already understand
that for most here (at least in this case), the divide is
substancially wide.

In other words, you're a crybaby who wanted free help, and
then bitched when you got what you paid for.
Nice.

[Erland Sommarskog]

 ([e-mail]) writes:

now we enter the realm of "horse-shit". i provided a description of the
tables, just not a create table script. not only that, but i also provided
sample data *and* sample output from the query *i* provided -
Yeah, you did. But you still did not manage did to explain the problem.

Believe it or not, when we suggest that you should post CREATE TABLE
and all that, it is to help you. And, to some extent a little test:
how much effort are you prepare to lay down yourself.
| You see, it's not that I'm paid to do this or anything.

cry me a river. i'm a "non-paid" contributer to 3 other newsgroups in
which, my technical prowess is appreciated and respected...

Maybe you are. That does however not mean that people here have any
particular reason to spend their free time to guess what you are up to.

And, of course, the attitude you have shown in no way encourages people
to help you.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[FreeBooks_at_2020ok_com]

Nothing is free. but you do get something free on the internet.
John S.
--------------------------------------------
http://2020ok.com - Free Online Books

[Stu]

oh, this thread has it all....

Drama, intrigue, and now, commercials!

/grabs popcorn