Error when perform query select left join select where

Question

I have 2 tables:

CREATE TABLE Student_registration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Contact VARCHAR(255) NOT NULL,
    regdatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE timestamp_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

When I perform this query:

select *
   from Student_registration
   LEFT JOIN (select count(*)
            from Student_registration)
    where RegDateTime > (select timestamp
                            from timestamp_table
                            where id= 1);

I get this error:

ERROR 1248 (42000): Every derived table must have its own alias

How do I fix this?

Answer 1

You should add alias (temporary name) here:

(select count(*) from Student_registration)

So it would be like this:

(select count(*) as total from Student_registration)

Here its total you can name it differently.

And also use on instead of where when using JOIN.