Which project or projects have the least number of employees?

Question

1.00/5 (1 vote)

See more:

which project or projects have the least number of employees?

Employee table has

-Fname
-minit
-lname
-ssn
-bdate
-address
-sex
-salary
-Super_ssn
-Dno

Department table has

-Dname
-Dnumber
-Mgr_ssn
-Mgr_start_date

Works_on table has

-essn
-pno
-hours

What I have tried:

I have tried the following but getting an error:

SQL

select pno, count(*) 
from works_on
group by pno
having count(*) = (
select min(CNT)
from (
select pno, count(*) as CNT
from works_on
group by pno ) );

The error I get is:

LIMIT 0, 1000	Error Code: 1248. Every derived table must have its own alias	0.000 sec

What am I missing?

Posted 15-Oct-22 18:55pm

SanjayMondal2001

Updated 15-Oct-22 19:13pm

Sandeep Mewara

v2

Add a Solution

Comments

Greg Utas 16-Oct-22 7:58am

The project that was cancelled. :)

2 solutions

Solution 1

If you don't understand an error message, google it:
Error Code: 1248. Every derived table must have its own alias - Google Search[^]
The top link explains the problem: https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql[^] (As do all the others ... )

In future, please try to do at least basic research yourself, and not waste your time or ours.

Posted 15-Oct-22 19:10pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2022-10-15T19:13:00

You need to provide the table alias in your inner queries SELECT. Add an alias after the closing bracket of the FROM clause subquery, i.e. each subquery in brackets must be given an alias (AS something)

Derived table is:

SQL

FROM ( subquery )

Above needs an alias, like:

SQL

FROM ( subquery ) AS anyName

Try:

SQL

select 
    pno, count(*) 
from 
    works_on
group by 
    pno
having 
    count(*) = (
        select 
            min(CNT)
        from (
            select 
                pno, count(*) as CNT
            from 
                works_on
            group by 
                pno 
            ) AS innerTable 
    );