RE: Basic SQL Help

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