[RESOLVED] Get the max value of two different data in two different tables

[aNubies]

Table1

ID | xDATE | NAME

1 | 9/20/11 | Cool
2 | 9/20/11 | Woo
1 | 9/20/12 | Cool
2 | 9/20/12 | Woo


Table2

ID | xDATE | NAME

1 | 9/20/12 | Cool
2 | 9/20/12 | Woo


I'm having a problem figuring this out, all I want to get is get the max value of those 2 different data in two different tables. The result that I wanted is

ID | xDATE | NAME

1 | 9/20/12 | Cool
2 | 9/20/12 | Woo


Help here please.

[honeybee]

Try a typical SELECT MAX query on a UNION of both these tables ??

.

[jmcilhinney]

The max what? Your example is useless because it doesn't exemplify anything.

[aNubies]

It seems the result is not what I expect sir.
@jm - sorry I forgot, max base on dates.

TABLE 1

ID | DATE | NAME

1 | 03/01/11 | SAMP1
2 | 03/01/11 | SAMP2
3 | 03/01/11 | SAMP3
4 | 03/01/11 | SAMP4


TABLE 2

ID | DATE | NAME

2 | 03/01/10 | SAMP2
4 | 03/01/10 | SAMP4


Here's another set of data:
The result should be the same with table 2. That is because those 2 records also in table1. But if one of those are not present in table 1 then the result might probably 1 record only.

[jmcilhinney]

You still haven't provided a proper explanation or a decent example. For a start, you say that you want to get the "max base on dates". That doesn't really make sense. Please provide a PROPER description. Also, your example has the same date in every record, so how does that help us see what you want.

Are you maybe saying, without actually saying, that you want one record for each ID that appears in both where the value for the date is the maximum from the two tables? If so, please say so. It's very frustrating when you're trying to help but you have to guess what's actually required. You know and we don;t, so you need to tell us. Assume that two many words are better than not enough.

[aNubies]

My apology, sorry for my bad english, anyway, here's another set of data:

Table 1

ID | DATE | NAME
2011-0002 | 2011-01-26 | Charles
2004-0001 | 2011-01-03 | Shanovski
2004-0002 | 2011-01-03 | Clayton
2011-0001 | 2011-01-18 | Crislie

Table 2


ID | DATE | NAME
2004-0001 | 2010-01-26 | Shanovski
2008-0002 | 2010-01-26 | Grey
2011-0001 | 2011-01-26 | Crislie

--------------

What Im trying to do is display the records by joining those two tables wherein a record is present on both tables using the Maximum of there dates MAX(DATE). In table 1 a user can appear multiple times but in table 2 user can only appears once. Now if I just going to Inner Join them too many records will be display, but all I need is base on their maximum dates. Hope it give you a hint what im trying to do.

[honeybee]

If table 2 will contain only one record per user, imo the max(date) won't make sense on this table.

Is this what you want:

Get each user record from table1 where the corresponding date is the max(date) for that user and then join this record with table2 data?

.

[aNubies]

By doing that,

SELECT MAX(DATE) FROM Table1

It only display one record, but they are lot of different records which they have their own maximum dates.

[jmcilhinney]

So, every ID in table 2 will appear in table 1, and every ID will always have the same name in both tables?

[aNubies]

@jm - yes indeed sir.

[FunkyDexter]

I'm struggling with your English a bit as well (good effort, though ) so I'm not sure this is the answer you want but I think you're after this:-

Select ID, Max(Date), Name
From 
  (Select ID, Date, Name From Table1
  Union
  Select ID, Date, Name From Table2)
Group By ID, Name

The inner query 'combines' the two tables as HoneyBee suggested. The outer takes that combined table and returns you the highest date for each ID and Name combination.

[aNubies]

I'm struggling with your English a bit as well (good effort, though )

Yeah, I know I'm still worst in English . But, gee thank you, FunkyDexter. That query is what I'm looking for . BTW is there any other alternatives rather than UNION ?

[FunkyDexter]

Not that I can think of. Union is the correct SQL keyword for combining two sets of data together 'vertically' (Joins combine them 'horizontally'). why don't you want to use it?

[aNubies]

Nah just looking for other alternatives. But anyway thanks .