query help

[xbl01234]

Hello;
I got 2 two tables which are Table 1(Thread) and Table 2(SubThread) from database.

I want to union two tables of the table 1 and table 2 together and get the final result as
table 4. The table 3 is result of the union of the table 1 and table 2.

After union the two tables, i want to
Select ThreadId, Username and PostIme from Table 3 where username="kam12"
and
if the threadId equal each other, pick up the row with the biggest value of the postTime,
after above, order all the row by PostTime DESC

My query as following, but it does work, and it give me the following massage when i run my query.could any one help me, please.

Every derived table must have its own alias //error massage it give me

SELECT *
FROM

(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread)

WHERE postTime
IN (
SELECT max( postTime )
FROM

(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread)

WHERE userName = "kam12"
GROUP BY threadId
)order by postTime;

[edited by: txbakers at 1:30 am (utc) on Feb. 6, 2008]
[edit reason] no urls [/edit]

[ZydoSEO]

I believe the 'derived' table(s) it's referring to is one or both of the FROM (SELECT...) highlited in bold below:

SELECT *
FROM
(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread)

WHERE postTime
IN (
SELECT max( postTime )
FROM

(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread)

WHERE userName = "kam12"
GROUP BY threadId
)order by postTime;

Try aliasing them both something like the following:

SELECT *
FROM
(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread) as DerivedTable1

WHERE postTime
IN (
SELECT max( postTime )
FROM

(select Id as threadId, userName, postTime from Thread
union
select threadId, userName, postTime from SubThread)as DerivedTable2

WHERE userName = "kam12"
GROUP BY threadId
)order by postTime;

[edited by: ZydoSEO at 4:32 pm (utc) on Feb. 4, 2008]

[xbl01234]

Thanks a lot, it works fine now.