Justin Willoughby wrote:
>
> I have two tables.
>
> One is a semi-permit table with a primary key the other is a transaction
> table. The transaction table has a primary key and also a column where I
> record the primary key from the first table.
>
> I write to both tables at the same time (inserts and updates to the first and
> inserts only two the second) The second table has a datestamp column and a few
> text fields.
>
> What I have been trying to do is so a select using a join but I only want to
> display the newest record from the second table that matches the primary key
> if the first.
>
> For example if I have:
>
> table1 table2
>
> pk text text2 pk ktbl1 text timestamp
> --------------- -----------------------
> 1 this that 1 1 him 19990501
> 2 that this 2 2 who 19990502
> 3 2 again 19990504
> 4 2 there 19990512
> 5 1 data 19990512
>
> So from my query I only want:
>
> table1.pk table1.text table2.pk table2.text table2.timestamp
> ------------------------------------------------------------
> 1 this 4 data 19990512
> 2 that 4 there 19990512
>
> That is I want to sort the first table with a where and have only a join for
> the second where the pk of the first table matches the newest timestamp of the
> second table.
>
> Can this only be done with a sub-select? I spent a while trying to figure out
> how to do it.
>
> I could hardcode the pk from the second table in the first on each update or
> insert but I don't know if this is really the best way to do it...
>
> Any suggestions?
>
> Thanks in advance,
>
> - Justin
Hi Justin
You can do this with subselect, or by using a temporary table to store the ktbll and
newest timestamp, before SELECTing from table1 JOINed with table2 JOINed with the
temporary table.
Example:
a)
CREATE TABLE
temp_table2
(ktbl1 INTEGER
,timestamp DATETIME
)
b)
INSERT INTO
temp_table2
SELECT
ktbl1
,MAX( timestamp +0 )
FROM
table2
GROUP BY
ktbl1
c)
SELECT
table1.pk
,table1.text
,table2.pk
,table2.text
,table2.timestamp
FROM
table1
,table2
,temp_table2
WHERE
table1.pk = table2.ktbl1
AND table2.ktbl1 = temp_table2.ktbl1
AND table2.timestamp = temp_table2.timestamp
d)
DROP
temp_table2
Tschau
Christian
