retrieve same field from multiple tables from tables list

[thegame8311]

okay this one is a bit tricky, but here's what I want to do

I have a table that has a list of certain tables that have names in them. Now not every table has the same names. I want a view of the names and the table that they are listed in.

here is an example:

TblList.dbf:
TableA
TableB
TableC

TableA.dbf
Name1
Name2
Name3

TableB.dbf
Name2
Name6
Name1

TableC.dbf
Name9
Name1
Name7

what I want is a list that looks like this

Name1 TableA, TableB, TableC
Name2 TableA, TableB

what is the best way for this a select statement or a query?

 

[Dan Freeman]

You have a true talent for asking unanswerable questions. ;-)

It looks at first blush like you've completely deconstructed what would otherwise be an easy cross-tab. I'll presume you have more than tableA-tableC, so there's that complication.

But you also have a history of changing the details once people start trying to answer you, so this could be something else entirely.

 

[thegame8311]

I do have more than just tableA-TableC

ok let me try to clear it up a bit

TblList.dbf

CustomersA
CustomersB
CustomersC

Now each of those customer tables have the same fields and structure
and one customer's name could be in each of them

CustomersA.dbf

John Smith
Joe Dodge
Mary Boyd

CustomersB.dbf

John Smith
Mary jones

CustomersC.dbf

Simon Wells

I want to query every table in the tbllist.dbf for "John Smith"

and display the name of the Table that he was found in.

So what would display in say a listbox control form would be

John Smith CustomersA
John Smith CustomersB

I'm not trying to be complicated and the solution might be simple like a Seek or something, but I'm not sure

 

[Olaf Doschke]

Actually a good solution would consolidate the tables into one customer table, which would simply remove the need to find out in which customer table a customer is. If the tables exist due to different types of customers or different sources of finding them, eg different websites you crawled, then you would put that info into an additional field and that would be what you query.

Simple rule, if you want to query something, make it data. Table names are not data in themselves, they are structure. And you're misusing them as data.

Bye, Olaf.

 

[IlyaRabyy]

Quick question, colleague TheGame8311: are those tables free tables, or they belong to a database? If the latter is true - you can query the data base (which is also a table).
This is just the idea off the top of my head, but HTH.


Regards,

Ilya

 

[JRB-Bldr]

Olaf raises a good point.

Its one thing to find an answer to your question, but a totally different thing (and a better thing) to advise you that your data table architecture is highly questionable.

each of those customer table
Why are you using multiple Customer Tables?
99.9999% of the time, you should have ONE Customer Table.

If your data table architecture was different, this problem would not exist and therefore you would not need an answer to this specific question.

And Dan has another good argument - You have a true talent for asking unanswerable questions

I don't know that I'd call your questions - unanswerable, but they are most often stated in a totally un-clear manner causing us to spend a good bit of time just trying to find out what you ACTUALLY want to know.

I'll add to that by asking why you have had SO MANY QUESTIONS in such a short time?

Just in the last month (since Jan 6, 2012), you have posted no less than 5 questions.

Now we sincerely want to help those who have questions, but when the questions just come one-after-another then we have to wonder about how much work you are really attempting to do yourself and how much you are just looking for others to do the work for you.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Actually 5 questions within 20 days are not a big deal. And you can't blame someone specifying himself as TechnicalUser not having the knowlegde or thought patterns of developers.

So just notice, thegame8311, you are doing things in an unusual and most certainly uneffective manner. And when we all think back to how we started, not necessarily in vfp, for me it was basic and 6502 assembler, then we all had to learn good practices.

As a technical user, it might be wiser to come earlier, in this case you could have asked about how to best design your database.

Sometimes (the 0.0001% jrbbldr mentions) you are confronted with an unusual design and have to keep it, but you can always extract data and make your own database with better design than to force a problematic feature on top of something unmanagable. This is just resulting in spaghetti code.

Often it also helps to tell us the real world problem, so we can come up instead of confronting us with restrictions that are a bad outset, no matter if they are made by others or you.

You may have many customer dbfs, because they ceom from different applications, but as you say they are structured in the smae way, it rather seems you designed them and they hold different customer types. And that would best be designed as a customertypeid field and a customertypes.dbf you reference from each customer.

Bye, Olaf.

 

[Dan Freeman]

What they said.

To Olaf, it's M:M not 1:M so customertypeID is on the right path, but not far enough. A customer can appear in more than one place.

This involves three tables, but absolutely not more than three. <s>

The original request was for a crosstab, and the second post wants something different, but the point is that both could be accomplished easily with properly structured data.

 

[Olaf Doschke]

M:M (or n:m). Yes, I see - a customer can be a row in several tables, this should not stay this way but be aggregated in a central table, so you need a cross reference table relating between customer types and main customer records.

If you also have employees and suppliers and normalize them into one core and several detail tables...

It's all quite easy, if you only know there is the topic database design and normalisation. Thegame looks like running before having learned to walk.

Bye, Olaf.