RE: Basic SQL Help -- MIDRANGE-L

Hi Charles -

Thanks...and that helps me understand what I want. And what you
described *is* what I want. That way I can generate a report that has
the hierarchy I need.

I'll study that SQL.


On Thu, 24 Jun 2004 14:30:40 -0400, CWilt@xxxxxxxxxxxx said:
> Michael,
> 
> Can you redefine the result set more accurately?
> 
> Are you asking for:
> 
> Collection1SKU, Collection1Desc, Collection1.Set1SKU, Set1Desc
> Collection1SKU, Collection1Desc, Collection1.Set2SKU, Set2Desc
> Collection2SKU, Collection2Desc, Collection2.Set3SKU, Set3Desc
> Collection2SKU, Collection2Desc, Collection2.Set4SKU, Set4Desc
> Collection2SKU, Collection2Desc, Collection2.Set5SKU, Set5Desc
> 
> I don't understand what you are looking for.  Actual sample data and
> sample
> results would be helpful
> 
> Remember, all rows contain the same number and type of data.  Each column
> comes from the same source.  The exception is you can use a UNION to join
> to
> separate results sets into one; but the # and types of columns must be
> the
> same.  
> 
> The above could be coded like so:
> 
> select FILEB.SKU, FILEC.DESC, FILEA.CHILD, C2.DESC
> from FILEB, FILEC, FILEA, FILEB B2, FILEC C2
> where FILEB.ID = 1 and FILEB.SKU=FILEC.SKU and FILEC.SKU=FILEA.PARENT,
>      and FILEA.CHILD = B2.SKU and B2.ID = 5 and B2.SKU = C2.SKU
> 
> It may help to see the inner joins explicitly:
> 
> select fileb.sku, filec.desc, filea.child, c2.desc
> from fileb inner join filec on fileb.sku = filec.sku 
>                 inner join filea on filec.sku = filea.parent
>                   inner join fileb as b2 on filea.child = b2.sku
>                                                      and b2.id = 5
>                     inner join filec as c2 on b2.sku = c2.sku
> where
>    fileb.id = 1              
> 
> 
> 
> Note: The above uses all inner joins, so there must be a matching record
> in
> every file to get a row to appear in the results set.  For example, if
> you've got a parent collection with no child sets, the join to FILEA
> would
> fail so you wouldn't see a record for that Collection.
> 
> 
> HTH,
> Charles.
> 
> 
> > -----Original Message-----
> > From: michaelr_41@xxxxxxxxxxxxxx [mailto:michaelr_41@xxxxxxxxxxxxxx]
> > Sent: Thursday, June 24, 2004 1:51 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Basic SQL Help
> > 
> > 
> > Argh...someone smarter than me (probably all) please help me with this
> > little SQL thing. Here's my deal:
> > 
> > 3 files
> > FILEA contains relationships (columns FILEA.PARENT and FILEA.CHILD),
> > where there is a 1-N relationship between parents and children. PARENT
> > and CHILD are SKU numbers. 
> > FILEB contains SKU numbers and identifiers (FILEB.SKU and FILEB.ID).
> > Valid IDs are 1=Collection, 5=Set, 9=Item. Collections can 
> > contain sets
> > and items.
> > FILEC contains SKU numbers and descriptive information (FILEC.SKU and
> > FILEC.DESC).
> > 
> > What I want is to have a result set (proper term?) that has this data
> > like this:
> > 
> > Collection1
> >   Set1
> >   Set2
> > Collection2
> >   Set3
> >   Set4
> >   Set5
> > .
> > .
> > .
> > Item
> > Item
> > Item
> > 
> > Where Set1 and Set2 are children of Collection1, Set3-Set5 
> > are children
> > of Collection2, etc. I would then like all the items to be 
> > after all the
> > collection/set information. Since each entity has descriptive
> > information, I need each result set row to have the SKU number and
> > descriptive information. I also need things ordered by a column in
> > FILE1. 
> > 
> > Whomever can send me an SQL statement that could make this 
> > work would be
> > a hero, and I would be happy to answer any communication or RPG
> > questions they have to the best of my ability. 
> > 
> > I know...I need to learn SQL better. 
> > 
> > TIA...
> > 
> > -- 
> >   
> >   michaelr_41@xxxxxxxxxxxxxx
> > 
> > --
> > This is the Midrange Systems Technical Discussion 
> > (MIDRANGE-L) mailing list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> > 
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
-- 
  
  michaelr_41@xxxxxxxxxxxxxx