Subquery with mysql with two tables

Asked

Viewed 89 times

0

Guys I’m trying to do a subquery in Where as a condition to list the average hours worked greater than 20 and the title of the codProj with 2 tables

Following the code:

SELECT projeto.codProj,trabalhaem.horas,AVG(trabalhaem.horas) AS media
FROM projeto INNER JOIN trabalhaem
ON projeto.codProj  = trabalhaem.codProj 
GROUP BY trabalhaem.horas
WHERE projeto.codProj in (SELECT titulo FROM projeto WHERE titulo = projeto.codProj) 
HAVING AVG(trabalhaem.horas) > 20;

table workIn: codEmp , codProj , hours

design table: codProj , title, codDepto ,

mysql sql select-sql

  • It seems to me that within your subquery you are trying to refer to a field from your external SELECT table. In this case assign nicknames to each occurrence of the table and use such nicknames in the references. In your case (SELECT title FROM project WHERE title = project.codProj) seems to me a little strange, there are two fields (codProj and title) with the same content in the project table?

  • i want to take the codProj and shows from him the corresponding title of that code tell truth to you to half lost in subquery why the class that the teacher pass this and I was not

  • But what do you want to get in your subquery? Describe it in English to try to understand it. What projects are these?

  • i want to print the average hours worked greater than 20 with the codProj and take this codProj and show from it the project title and has to be with a subquery

  • From what you described it is not necessary to use subquery. It makes no sense to put work.hours and AVG(work.hours) simultaneously in the list of fields of your SELECT and nor do GROUP BY by work. Again: describe in Portuguese what you want to get and post the definition of the tables involved.

  • then guy the exercise asked to do with nested query

Show 1 more comment

1 answer

0

From what I understand of your problem, try:

SELECT projeto.codProj, projeto.titulo, AVG(trabalhaem.horas)
FROM projeto INNER JOIN trabalhaem ON (projeto.codProj = trabalhaem.codProj)
HAVING (AVG(trabalhaem.horas) > 20)
GROUP BY projeto.codigo, projeto.titulo;

Browser other questions tagged mysql sql select-sql

You are not signed in. Login or sign up in order to post.