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