CODEWITHSUNDEEP
phpmysqlsqldatabasejoin
manju
manju

Reputation: 161

How to use properly JOIN condition with Subquery in WHERE?

Currently my schema looks like this:

CREATE TABLE IF NOT EXISTS `hours` (
  `Project_ID` varchar(10) NOT NULL,
  `Project_Name` varchar(50) NOT NULL,
  `Res_ID` varchar(40) NOT NULL,
  `Date` date NOT NULL,
  `Hours` int(10) NOT NULL,
)

CREATE TABLE IF NOT EXISTS `project_resources` (
  `Project_ID` varchar(10) NOT NULL,
  `Res_ID` varchar(40) NOT NULL
)

//A single project Id can be assosiated with many resource id's

CREATE TABLE IF NOT EXISTS `resources` (
  `Res_ID` varchar(40) NOT NULL,
  `Res_Name` varchar(50) NOT NULL,
  `Email` varchar(50) NOT NULL,
  `Phone_Number` bigint(12) NOT NULL,
  `Reporting_Manager` varchar(50) NOT NULL,
  `Role` varchar(50) NOT NULL,
  `Designation` varchar(50) NOT NULL,
  `Password` varchar(50) NOT NULL
)

Here I am trying to generate a query such that it displays the data in the below format,

Resource Name | Sum(Hours).

I tried executing the following query

SELECT res_name,sum(hours) FROM hours h
INNER JOIN resources r ON h.res_id=r.res_id
  WHERE r.res_id = (SELECT res_id FROM `project_resources` WHERE project_id='someproject')

I know this returns subquery returns more than 1 row error. But I was just wondering what I can do to get this query right.

Upvotes: 1

Views: 90

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are selecting total hours over all projects and their ressources that are (also) used in 'someproject'. As there can be more then one ressource associated with 'someproject', use IN to get them all. Then ...

  • either group by res_name (provided it is unique, else use res_id) to get a result record per ressource
  • or remove res_name from your result (because when there are more than one, you would only show one of them randomly)
  • or generate a string containing all ressource names with GROUP_CONCAT

So either:

SELECT 
  res_name,
  sum(hours) 
FROM hours h
INNER JOIN resources r ON h.res_id=r.res_id
WHERE r.res_id IN 
(
  SELECT res_id 
  FROM `project_resources` 
  WHERE project_id='someproject'
)
GROUP BY res_id;

Or:

SELECT 
  GROUP_CONCAT(res_name) AS res_names,
  sum(hours) 
FROM hours h
INNER JOIN resources r ON h.res_id=r.res_id
WHERE r.res_id IN
(
  SELECT res_id 
  FROM `project_resources` 
  WHERE project_id='someproject'
);

Upvotes: 0

Choudhury Saadmaan Mahmid
Choudhury Saadmaan Mahmid

Reputation: 1316

So basically, you want to show 2 things:

  1. Name of the resource
  2. Total amount of hours the resource worked

Under the condition that your Res_ID is in project_resources table and the project id is 'someproject'.

Right?

Then let's break this problem into three small parts:

Part - 1:

To get the name of the resource you should write:

SELECT rs.Res_Name
FROM resources rs

Note that rs is the name of the alias of the table resources.

Ok?

Now Part - 2:

To get the total amount of hours the resource worked you should write:

SELECT SUM(Hours)
FROM hours h

Basically, h is the alias of the table hours. I think you got it, right?

Finally, Part - 3:

Your Project_ID should be 'someproject'.

Also, the Res_ID should be inside project_resources.

Now, let's join all the parts together. Now we get:

 SELECT r.Res_Name, SUM(Hours)
 FROM hours h
 INNER JOIN resources r ON h.Res_ID = r.Res_ID
 INNER JOIN project_resources pr ON r.Res_ID = pr.Res_ID
 WHERE pr.Project_ID = 'someproject'

Basically, here we've first joined hours with resources given the fact that Res_ID is same in both tables, also we joined the table resources with project_resources given the fact that Res_ID is same in both tables, and that Project_ID of hours is 'someproject'.

Hopefully, this will give you what you want. You got the idea, right?

However, a word of caution. I've noticed that you're using the same name for table hours and it's column Hours. Although, this won't cause any problem because of the case difference in the names, but this is not really a good practice. You should think of a different, meaningful name for your column to avoid confusion and any kinds of unwanted occurences. Enjoy coding!!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can just use in:

Select res_name,sum(hours) 
from hours h inner join
     resources r
     on h.res_id = r.res_id 
where r.res_id in (SELECT res_id 
                   FROM `project_resources` 
                   WHERE project_id = 'someproject'
                  );

However, I might suggest just doing multiple joins:

Select res_name,sum(hours) 
from hours h inner join
     resources r
     on h.res_id = r.res_id inner join
     project_resources pr
     on pr.res_id = r.res_id and pr.project_id = 'someproject'

Of course, this will not work if you have duplicates in the project_resources table.

Upvotes: 0

Keshav jha
Keshav jha

Reputation: 1364

I think this will help you

Select res_name,sum(hours) 
from hours h inner join resources r on h.res_id=r.res_id 
where r.res_id IN (
      SELECT res_id 
      FROM `project_resources` 
      WHERE project_id='someproject'
)

you can use 'IN' clause in your where statement if your sub query return more than 1 rows

Upvotes: 1

Related Questions