Perform a LEFT Join on Derived tables failing with aliases

Question

New to SQL. I am trying to perform a LEFT JOIN on two derived tables but I am getting an error Every Derived Table must have an alias. I did provide aliases to them but it still gives the same error message. Following is the query:

SELECT * FROM 
    (SELECT first_name 
        from users 
        inner join messages 
        on (users.id=messages.from_user_id)
        where to_user_id=1 
        group by first_name order by message_date) AS DATAONE 
            LEFT JOIN
                (select * from 
                    (SELECT count(message_id),u.id,first_name,message_subject,message_status 
                    from users u 
                    inner join messages m 
                    on (u.id=from_user_id)
                    where to_user_id=1 and message_status = 2 
                    group by first_name order by message_date) as datatwo) 
        on (dataone.first_name = datatwo.first_name)

Please advice.

Answer 1

You forgot one:

SELECT * 
FROM     
(
   SELECT first_name 
   from users 
   inner join messages on users.id = messages.from_user_id
   where to_user_id = 1 
   group by first_name 
   order by message_date
) AS DATAONE 
LEFT JOIN
(
   select * 
   from 
   (
       SELECT
         count(message_id),
         u.id,
         first_name,
         message_subject,
         message_status 
       from users u 
       inner join messages m on u.id = from_user_id
       where to_user_id     = 1
         and message_status = 2 
       group by first_name order by message_date
   ) as datatwo
) AS Sub   -- <------- You forgot to put an alias here
on dataone.first_name = Sub.first_name

Or:

You forgot one:

SELECT * 
FROM     
(
   SELECT first_name 
   from users 
   inner join messages  on (users.id=messages.from_user_id)
   where to_user_id=1 
   group by first_name order by message_date
) AS DATAONE 
LEFT JOIN
(
       SELECT
         count(message_id),
         u.id,
         first_name,
         message_subject,
         message_status 
       from users u 
       inner join messages m  on (u.id=from_user_id)
       where to_user_id     = 1 
         and message_status = 2 
       group by first_name
       order by message_date
) as datatwo    -- <------- You forgot to put an alias here
on (dataone.first_name = datatwo.first_name)