SELECT statement generates fields from data in secondary table???

[VBAjedi]

I'm just getting my feet wet in mySQL (actually, I just got thrown in over my head!), so I apologize for not being able to define my question in a more succinct manner. I need to construct a SELECT statement that pulls data from two tables (A & B, neither of them are my design). In addition to columns "InvoiceAmt" and "Salesperson" from table A, the returned data needs to have three columns: "Country", "State/Province", and "City".

Table A has sales data, including columns named "InvoiceAmt", "Salesperson", and "RegionID".

Table B has region information, with columns structured as follows:
"RegionID": unique identifier index number
"RegionDescription": A country, state, OR city name
"ComesUnder": Contains RegionID this RegionID comes under (like Texas comes under USA), or 0 if none
"ComesAbove": comma-delimited list of RegionID's this RegionID comes above (like Texas comes above Dallas and San Antonio), or blank if none. Data from Table B might look like:

1 | USA | 0 | 2
2 | Texas | 1 | 3,4
3 | Dallas | 2 |
4 | San Antonio | 2 |

I honestly don't know where to begin. Can anyone give me pointers on how to structure a query that will populate as many of the three columns (Country, State, City) as possible for each sales record? For example, if a sales record has the RegionID for Texas, the returned result for that record would include Country = "USA", State = "Texas", and City = (blank).

Thank you!

VBAjedi

 

[sleipnir214]

something like:

select * from TableA, TableB where TableA.RegionID = TableB.RegionID

will fetch each sales record and region data pertinent to that record. You can then pare down the number of columns returned by replacing the "*" with a comma-delimited list of column names you need.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[VBAjedi]

Thanks for the quick reply! The "where TableA.RegionID = TableB.RegionID" bit is certainly the first step.

However, unless I'm missing something, it isn't just a matter of "paring down" the data. I need to GENERATE data from the matching Table B record's data. The records in Table B each hold a single levels description, along with information about where that record fits in a hierarchy. I need to extrapolate all three levels of that hierarchy into three columns for each matching Table B record.

It's a bit difficult for me to articulate clearly - please review the example in my original post.

Thanks!

VBAjedi

 

[sleipnir214]

Oops. Sorry -- I didn't read your original post right.

Man, that's one screwed up database schema.

For a sales record from Dallas, TX, what will the RegionID of that sales record be?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[VBAjedi]

Just got back in the office. . .

Yes, it's a messed-up table design, but I can't change it. . . it's generated by a web component that is outsourced.

You can't really derive anything from the RegionID field - it's just a unique integer used as a table key. The RegionID for "USA" could be "1124", and "Dallas" could be "1125".

I've gotta figure out how to do this! Why can't my first go at mySQL be a little less freakish? [LOL]

VBAjedi

 

[sleipnir214]

I understand that the ID could be any of the three. Does the database consistently use one of the three levels of region codes? Does it always use the city region code?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[VBAjedi]

Well, it does use the city code about 80% of the time. The other 20% of the data is either sales that were'nt classified any lower than the state level, or budget projections that aren't for a specific city (state-level or country-level projections).

Did you think of a way to do it if I primarily was dealing with city codes?

VBAjedi

 

[VBAjedi]

Ok, I think I figured out a way. . . I only have to work UP from the supplied TableA.RegionID (if the RegionID is at the Country level I don't care what's under it anyway!). So I can left join the supplied TableA.RegionID to the matching TableB.RegionID, then left join TableB.ComesUnder to (duplicate) TableB1.RegionID. If there's a TableB1 match, I have found the region above the TableA.RegionID. Finally, I can left join TableB1.ComesUnder to (duplicate) TableB2.RegionID to check for a potential top region.

Clear as mud? FYI, yes, this is related to my latest post here in this forum. . .

VBAjedi