|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.