MySQL help

lordgreg

i want to tables: FOO and BAR.

both tables have different columns except two. COL1 and COL2. what i want is, to select that two columns from both tables and ORDER it by COL1 and then fetch only the last one row...

please help,

thanks,

gregor

craigh

not sure I understand. are the two tabels related: i.e. the values in Col1 and Col2 are the same. Or do you want to select all the records in both columns from both tables in UNION and then select the most recent? If most recent, do you have a timestamp or date column?

lordgreg

Or do you want to select all the records in both columns from both tables in UNION and then select the most recent? If most recent, do you have a timestamp or date column?

-------- yes, that's it.. and i have a date column.. all i want is, to fetch the last date column with userid column.. that's all what i need.

Anon

I think this does what you ask, but I haven't tested it, but it should give you the general idea.

SELECT table1.col1 AS colA, table1.col2 AS calB FROM table1

UNION SELECT table2.col1 AS colA, table2.col2 AS colB

ORDER BY date DESCENDING LIMIT 1;

lordgreg

Craig: i still can't get it to work. exactly example by me is:

select forum_posts.userid AS colA, forum_posts.date AS colB FROM forum_posts UNION SELECT forum_thread.userid AS colA, forum_thread.date AS colB FROM forum_thread ORDER BY date DESC LIMIT 1

MySQL said: You have an error in your SQL syntax near 'UNION SELECT forum_thread.userid AS colA, forum_thread.date AS colB FROM forum_t' at line 1

and it doesn't work..

please help anyone.. thanks

Anon

Apparently MySQL doesn't support the SELECT UNION function (MS Access does and I thought MySQL did too).

I guess you will have to do a bit more research and figure out a way to do two separate SELECT statements (which return an array) and then concatanate the arrays to make them all one result set. Sounds possible, I just don't know how to do it.

sorry.

Anon

OK:

You can check out the function array_merge at php.net but it is also possible to use a simple "+"

i.e. $newarray=$array1+$array2

this would produce different results than:

$newarray= array_merge($array1, $array2)

you'd have to read the documentation (and the comments on the bottom) to get more information.

Unfortunately, you'll have to then sort the array (put it in order) and then extract the first (or last) value as you need it. Quite a bit of work for what you need, but do-able.

Good luck and let me know how it turns out!