Help with LO Base

users
#1

Hello.

I'm a new user of LO Base, and I have some problems that I haven't be able
to solve with tutorials.

I'm making a db with several tables with many records in each one, and I
want to retrieve records from several tables at the same time. Let me
explain my problem:

I have a "table A" with about 12000 records, with their IDs 1-12000. Then I
have other tables where some of those records have more data, and some
records are not present. I mean, in Table B, there are records 1-1000, in
table C 1001-2000, not necessary in that orther, but the point is that some
records are present in the tables are some aren't.

When I try to retrieve data from several tables, I'd want to have all the
records, no matter if they are present in all the tables or not.

I don't know what kind of relationship I need to create so when I run a
query to retrieve data from many tables at the same time, if a record is not
present in a table, then the columns for that record in that table should be
empty, but instead of that i'm getting things as the correct data from table
A, but then in table B I only get one record repeating in all the rows.

I hope I've explained myself clearly enough so you can help me.

Thank you very much.

#2

Hi :slight_smile:
I think the tables that have information for only some of the 'people' in
Table A need to have a dummy record to cover that case.

So you might have 1 row of all zeros, or whatever is suitable to indicate a
lack of data. Then all the people with no data just point to that single
row.

Err, i have a feeling you have already done that but it's the simplest
thing that might have gone wrong.
Regards from
Tom :slight_smile:

#3

Hello Victor,

I don't know how deep you are into relational database theory, but I suppose
you have defined all the necessary "foreign-keys" (= IDs) linking the tables
together. Having done so you have to use (eg.) a "left outer join" to "combine"
the correct info in the various tables. If you used only an "ordinary" join
you would not get tuples where there are no corresponding "IDs" in some tables.
I usually create some kind of "view" for such problems, e.g.

create view xyz as
select c1,c2,...,cx from
  ((tableA as
   left outer join tableB b on b.cxyz = a.czyx)
   left outer join tableC c on c....)
where ....
sort by ...
;
Then you would select (or maybe filter further) from the view.
Regards
H.S.

#4

Hello.

I'm a new user of LO Base, and I have some problems that I haven't be able
to solve with tutorials.

I'm making a db with several tables with many records in each one, and I
want to retrieve records from several tables at the same time. Let me
explain my problem:

I have a "table A" with about 12000 records, with their IDs 1-12000. Then I
have other tables where some of those records have more data, and some
records are not present. I mean, in Table B, there are records 1-1000, in
table C 1001-2000, not necessary in that orther, but the point is that some
records are present in the tables are some aren't.

When I try to retrieve data from several tables, I'd want to have all the
records, no matter if they are present in all the tables or not.

I don't know what kind of relationship I need to create so when I run a

if you made a INNER JOIN then you have only the data who there are records in both tables
if you made a LEFT OUTER JOIN you will have all records in the left table + all records (also the empty in the right table.

so start the selection (left) with the table where all records are present and join to tables with aditional information

#5

What is the reason you need to split your data into multiple tables?

You will make life a lot easier for yourself if you are able to consolidate
your data into one table. The rules of data 'normalization' (see link below)
may require your data to be divided into more than a single table, but this
does not seem like the reason why your data is divided as you describe.

http://databases.about.com/od/specificproducts/a/normalization.htm

#6

Hi :slight_smile:
Relational databases have been around for quite a long time now.

When many records/rows contain the same data it helps if that data can be
held separately so that only 1 instance is held with the many rows simply
referencing that 1 instance. That 1 instance can then be edited or updated
affecting all the rows/records simultaneously without any worry that some
records might not have received the change. It keeps data consistent. It
reduces the amount of storage space the database takes up.

Of course, like almost any tool, it can be taken to a ridiculous extreme of
diminishing returns where it becomes worthwhile considering alternatives
but generally it is a good way of making a database more efficient.
Regards from
Tom :slight_smile: