[SOLVED] SQLite3 Joining Tables
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2014-11-11 01:30

Hello everyone:

Although I've made a workaround, I'd still like to know what I'm doing
wrong. Maybe one of you can tell me.
I have two tables: ATTENDEES || HISTORY setup on two DBGrids.
The schemas are:

Code: [Select]ATTENDEES(
                ATTENDEE_ID INTEGER PRIMARY KEY,
                L_NAME VARCHAR(15) NOT NULL,
                F_NAME VARCHAR(15) NOT NULL,
                AGE VARCHAR(2) NOT NULL,
                PHONE_NUMBER VARCHAR(14),
                ADDRESS VARCHAR(35) NOT NULL,
                CITY VARCHAR(20) NOT NULL,
                STATE VARCHAR(2) NOT NULL,
                ZIPCODE VARCHAR(5) NOT NULL,
                START_DATE VARCHAR(10) NOT NULL,
                END_DATE VARCHAR(10) NOT NULL
);

HISTORY(
                HISTORY_ID INTEGER PRIMARY KEY,
                L_NAME VARCHAR(15) NOT NULL,
                F_NAME VARCHAR(15) NOT NULL,
                LAST_PAYMENT_DATE VARCHAR(10) NOT NULL,
                CURRENT_PAYMENT_DATE VARCHAR(10) NOT NULL,
                SUBSIDY VARCHAR(1) NOT NULL,
                AMOUNT_PAID REAL NOT NULL,
                BALANCE REAL NOT NULL,
                FK_ATTENDEE_ID INTEGER,
                FOREIGN KEY(FK_ATTENDEE_ID) REFERENCES
ATTENDEES(ATTENDEE_ID)
);

When I select a record on the DBGrid for ATTENDEES, I want the payment
history for that record to be pulled up on the other DBGrid for
HISTORY. My Query is:

Code: [Select]Query2.SQL.Text:= 'SELECT * FROM HISTORY INNER JOIN
ATTENDEES WHERE HISTORY.FK_ATTENDEE_ID = ATTENDEES.ATTENDEE_ID';
Query2.SQL.ExecSQL;

But it doesn't work. For some reason it pulls up all the history
records for everyone.

So I did this:

Code: [Select]attendeeID:=
IntToStr(Query1.FieldByName('attendee_id').Value);
Query2.SQL.Text:= 'SELECT * FROM HISTORY WHERE FK_ATTENDEE_ID =
'+attendeeID;
Query2.ExecSQL;

And that works fine, but I'd still like to be able to join the tables
and use the Foreign Key the way it's suppose to be done. Any help on
this would be appreciated.

Landslyde

----------------------------------------------------------------------
mangakissa · 2014-11-11 08:30

A foreign key is just a relation of two tables which first table
records can't be deleted if they still exists in the second table.
That's only the relation to these two. If you use a field as relation
to history the field values should be the same. The 'relation field'
to Attendees have to be indexed.
Code: [Select]CREATE TABLE ATTENDEES (
                ATTENDEE_ID INTEGER PRIMARY KEY,
                L_NAME VARCHAR(15) NOT NULL,
                F_NAME VARCHAR(15) NOT NULL,
                AGE VARCHAR(2) NOT NULL,
                PHONE_NUMBER VARCHAR(14),
                ADDRESS VARCHAR(35) NOT NULL,
                CITY VARCHAR(20) NOT NULL,
                STATE VARCHAR(2) NOT NULL,
                ZIPCODE VARCHAR(5) NOT NULL,
                START_DATE VARCHAR(10) NOT NULL,
                END_DATE VARCHAR(10) NOT NULL
);

CREATE HISTORY (
                HISTORY_ID INTEGER PRIMARY KEY,
                ATTENDEE_ID INTEGER,
                LAST_PAYMENT_DATE VARCHAR(10) NOT NULL,
                CURRENT_PAYMENT_DATE VARCHAR(10) NOT NULL,
                SUBSIDY VARCHAR(1) NOT NULL,
                AMOUNT_PAID REAL NOT NULL,
                BALANCE REAL NOT NULL);
This query selects all records and shows the related fields founded
with field  ATTENDEE_ID
Code: [Select]SELECT H.AMOUNT_PAID,H.LAST_PAYMENT_DATE, A.L_NAME,
A.F_NAME  FROM HISTORY H
  INNER JOIN ATTENDEES A ON H.ATTENDEE_ID = A.ATTENDEE_IDThis query
shows all records of HISTORY with a selection of a givven relation.
Code: [Select]SELECT * FROM HISTORY WHERE ATTENDEE_ID = 100Using
database components (ZEOS, SQLdb) you can prepare a query with your
own givven selection
Code: [Select]begin
  SQLQuery1.SQL.text := SELECT * FROM HISTORY WHERE ATTENDEE_ID = :ID;
  SQLQuery1.Params[0].AsInteger := 100
  SQLQuery1.active := true;
end;An index speeds up you query. If you create a index on ATTENDEE,
the database engine sear in the indexfile fo related records nstead of
read the the sequently.

Read these 4 tutorials. They will givven you the best knowledge using
SQLDB or other databasecomponents