Compare the date value in different tables

[johngordon]

I have a main table which is joined to a number of related tables.

The main table contains core details of lodges, and the other tabes contain information much of which can be one to many, e.g. news updates, or special offers.

This has the advantage of being able to tell is when each of these was last updated.

However, I know have a requirement to show a single date of when the last time any of a properties information was last updated.

How much of a pain is that to get from the data?

As an example, some of the tables are:

lodges
LodgeID (PK)
Lodge
LastUpdated

news_updates
NewsID (PK)
LodgeID (FK)
LastUpdated

special_offers
OfferID (PK)
LodgeID (FK)
LastUpdated

Assuming a lodge has at least one news update and at least one special offer, I guess I need a query something like:

SELECT 
lodges.Lodge
, lodges.LastUpdated
, news_updates.LastUpdated
, special_offers.lastUpdated
INNER JOIN news_updates
ON lodges.LodgeID = news_updates.LodgeID
INNER JOIN special_offers
ON lodges.LodgeID = special_offers.LodgeID

But how could I then compare the three LastUpdated values and display only the most recent?

[DaveMaxwell]

I think this will work…note that greatest will return NULL if any of the compared values are null…

SELECT lodges.Lodge
     , GREATEST(lodges.LastUpdated
              , news_updates.LastUpdated
              , special_offers.lastUpdated) AS MostRecentUpdateDate
 INNER JOIN news_updates ON lodges.LodgeID = news_updates.LodgeID
 INNER JOIN special_offers ON lodges.LodgeID = special_offers.LodgeID

Reference: http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_greatest

[johngordon]

Thanks Dave - I might need to play around with the joins to account for blanks, but that GREATEST command should definitely get me started.

[r937]

i dunno, man… you’re cross-joining the many rows of news updates with the many rows of special offers

and there are other tables

a better approach is a UNION of MAXes, with a placeholder column for type of table updated, e.g. ‘news-update’ and ‘special-offer’

that gives you the latest updates for each types of tables per lodge

UNION means no need to worry about some lodge happening not to have any one type, e.g. special offers – which would mean your joins would have to be outer

[Elizine]

Using relational operators:

SELECT * FROM TableA
UNION 
SELECT * FROM TableB
EXCEPT 
SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;
Change EXCEPT to MINUS for Oracle.

Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:

Expressions in parentheses
INTERSECT
EXCEPT and UNION evaluated from left to right.

[johngordon]

Thanks for the other pointers - will have a play around at some point.

In the meantime, we’re going to use the LastLogin date for now. Although that has presented another issue - have started a separate thread about that.

[r937]

[quote=“Elizine, post:5, topic:215882, full:true”]
Using relational operators…[/quote]

i’m having a real hard time understanding how this, um, contribution relates to OP’s question