[SOLVED] How to eliminate potential rows on table joins.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

[SOLVED] How to eliminate potential rows on table joins.

Post by impulse() »

In table A I have several unique rows then table B has several entries for each row on table A. When I join the tables I want only the latest entry from table B to be joined to table A on their unique IDs.

The latest entry can be determined by a unix timestamp on table A but my queries seems to be failing. I'm trying to use this at the moment but it selecting all rows from table B on the join, not just the one with the highest unix timestamp.

Code: Select all

select max(b.timestamp), a.*
from tableA as a, tableB as b
where a.id = b.backupID
and a.customer != 'APT'
and b.apt = 'n'
group by b.timestamp
But this joins to every row in table B not just the row with the highest timestamp.
Last edited by impulse() on Tue Sep 11, 2007 3:35 am, edited 1 time in total.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

This is now solved. The query I used in the end was:

Code: Select all

SELECT DISTINCT (b.backupID), max( b.timestamp ) , a. *
FROM tableb AS b, tablea AS a
WHERE a.id = b.backupID
AND a.customer != 'APT'
AND b.apt = 'n'
GROUP BY backupID
If anybody else knows other way to do this I'd be interested to know.
Post Reply