List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 20 1999 6:01pm
Subject:Re: Do I need a sub-select for this (I know MySQL does not yet support it)
View as plain text  
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

Thread
Do I need a sub-select for this (I know MySQL does not yet support it)Justin Willoughby20 May
Re: Do I need a sub-select for this (I know MySQL does not yet support it)Christian Mack20 May