Multiple table queries

[yianni]

Is it possible to set up multiple table queries using Base currently?

Thank you!!

[MSPhobe]

Multi- table queries and forms....

I was playing about with these, and have discovered the following so far....

(P.S.: Since this was posted, I have converted it to a more tightly edited version, which can be found at....

http://sheepdogguides.com/fdb/fdb1mfrm.htm

... end "P.S.")

What follows may seem like an awful lot of work... a bit of an elephant gun for the mouse in my sights. However, remember it is just to illustrate something... and that something entail important concepts. One quickly grasped advantage of doing this sort of thing this way, the "hard" way, is that, for instance, if you mistype Bach's name, it will be obvious... every reference to Bach will be wrong, AND you only have to fix one entry to fix all of the manifestations of the error. Also, you can ESSENTIALLY (without actually) "type" "Wolfgang Amadeus Mozart" dozens of times, simply by typing just "wamo" many those dozens of times. By the way... did you know that Mozart was also known as WG Mozart? More on this later, when you've puzzled on it. Turning back to forms displaing data from multiple tables...

Imagine a world where every audio CD consisted of music by just one composer. (Many composers, but only one on any given CD.) Imagine also that each CD has a unique code, a bit like an ISBN, but much shorter!

Set up two tables:

________

First table: "CompNam" Composer's Names
Fields:
Primary key :CompNameID- 4 characters, text
CompName- 20 characters. (Human friendly version)

Sample data:

bach / JS Bach
wamo / WA Mozart
..etc...

________

Second table: "Albums"
Fields:
Primary key: AlbumID- 8 characters, text
ComposerByCode- 4 characters, text
AlbumName- 20 chars, text.

===
Diversion: WA/ WG Mozart? Figure it out? Hint: The G was for Gottlieb.

===
The only tricky bit is as follows:

Table 1 has the field "CompNameID". In that table, each composer appears only once, the values in the CompNameID field have to be unique.

Table 2 has the ComposerByCode field. This field should only ever have something in it which ALSO appears in the first table's "CompNameID" field. I've given those fields different names in this example to make things easier, but in real databases, it would not be unusual for them to have the same name. Furthermore, a shorthand for "The the first table's CompNameID field" is: CompNam.CompNameID: The table's name, a dot, the field's name.

In table 2, "wamo" can appear as many times as you like. (Remember it can apear only once in the first table.)

Second table sample data....

Album ID/ Composer / Album name

bob1 / bach / Best of Johann Bach
bfc1 / bach / Bach Flute Concertos
mmot / wamo / Marvelous Mozart
... etc...

===
Once you have set up those tables, you can make a query draw just what you need from the two of them. If you get every record twice, as you may well do at first, the extra thing you need to do (you do need to do it... it's just that you may have done it "automatically") is to declare that there is a relationship, a link, between the following fields:

CompNam.CompNameID
Albums.ComposerByCode

(It is, obviously, I hope, essential that the field type, including the length specification, for the two fields be identical.)

That query can be the basis of a form, and the form would display....

Album ID / Album name / Composer

bob1 / Best of Johann Bach / JS Bach
bfc1 / Bach Flute Concertos / JS Bach
mmot / Marvelous Mozart / WA Mozart

The form displays what you need to know, but "hidden under the hood" is a better way of handling the composer's names. I singled them out for the special handling because, without it, I would be entering the same thing over and over. Admittedly, I now have to enter the code for the composer over and over... but what I'm entering is more concise. Also, if you just put the composer's name in where I'm using a code to link a record from table 2 (the album details table) to a record in the other table, inconsitencies would creep in: "JS Bach", "J.S. Bach", "Bach", etc. Computer managed jobs thrive on the elimination of inconsistencies!

Remember: This is just an illustration, so don't be surprised if you wouldn't necessarily do this job this way.

===
A frustration: I've not found a way to set up a similar, data-from-more-than-one-table form that allows me to edit data in the underlying tables. Any ideas welcomed!

===
If you look in the ooBase help file under "Relations- Creating and Deleting", you get some information on that. I found that I could see the Edit- Database- Advanced option for my ooBase native database... but that the option was greyed out, not available. Is ooBase not fully a relational database? (Shock, horror!) In the notes above, we "create a relationship", but that appears to be just part of the selection process. (You can learn more my looking at the underlying SQL code.) What I was thwarted from doing was creating relationships between tables at a more fundamental level... and the help file does suggest that not all databases are capable of this. Remember that when you work with ooBase, you are mostly working with a front end which can communicate with many databases. I concentrate on using it to communicate with the underlying database which is supplied with OpenOffice version 2, the derivative of HSQL. I'll be disappointed if that can't have relationships between tables... but if someone knows I can't have them... please let me know?!

===
Diversion: Gottlieb? Amadeus? Both say "beloved of God." One in Wolfie's native tongue, one in the Italian which was trendy for composers.

Tom

http://sheepdogguides.com/fdb/fdb1main.htm

[pierdeux]

[DrewJensen]

In the base window open the relationship editor....

Tools>Relationships

You will need to to add the tables you want to edtor...then drag columns from one table to the other to form the relationship.. (you can also use the menu Insert>New relation..' )

A line will be drawn between the two tables, you can double click this line ( or right click and select edit) to bring up the options dialog for this relationship.

Of course one can also create these using standard SQL DDL commands in the SQL window. If you check out the HSQL users manual you will find references for how to do this using the ALTER TABLE command, or even direftly in a CREATE TABLE command.
_________________
Blog - http://baseanswers.spaces.live.com/

[yianni]

Thanks for all your help!