Nested MySQL Query w/ concat and adddate

Question

I am trying to nest a few queries but so far am getting back error 1242: Subquery returns more than 1 row. I want more than one row, as I am working on a number of records.

I have 2 tables. One has a commencement date stored in 3 columns; yr_comm, mth_comm, day_comm. The 2nd table has a period of service (in years) for a number of users which is expressed as an integer (2.71, 3.45, etc).

I need to take this start date (from table 1), and add on the period of service (from table 2) to obtain an end date, but I only need to display the year.

I have 2 queries which work just fine when seperate, they result in the required values, however I am having trouble combining the queries to get the desired end result.

Query 1: Concatenate the 3 commencement values into date format

SELECT concat_ws('-', yr_comm, mth_comm, day_comm) AS date_comm
FROM table 1

Query 2: Convert the integer yrs_service into days

SELECT format(yrs_served * 365, 0)  AS days_served
FROM table 2

Query 3: Use date_add function to add the days service to the commencement date

SELECT date_add(date_comm, INTERVAL days_served DAY) AS date_left

Can anyone suggest how I can achieve the above? Many thanks in advance.

EDIT - Here is the full query I am working on:

SELECT prime_minister.pm_name, yr_comm, party, ADDDATE(
    (SELECT CONCAT_WS('-', yr_comm, mth_comm, day_comm) FROM ministry), INTERVAL 
    (SELECT FORMAT(yrs_served * 365, 0) FROM prime_minister) YEAR) AS date_left
FROM ministry JOIN prime_minister USING (pm_name)
WHERE party NOT LIKE '%labor%'
AND prime_minister.pm_name = ministry.pm_name
ORDER BY pm_name;

Answer 1

you can use user variables

SET @date = CONCAT_WS('-', 2012,1,1);   -- paste your query here
SET @toAdd = (SELECT MONTH(CURDATE())); -- paste your query here
SELECT DATE_ADD(@date, INTERVAL @toAdd DAY) AS date_left

• SQLFiddle Demo

which is the same as

SET @date = CONCAT_WS('-', 2012,1,1);   -- paste your query here
SET @toAdd = (SELECT MONTH(CURDATE())); -- paste your query here
SELECT @date + INTERVAL @toAdd DAY AS date_left

• SQLFiddle Demo

or without using variable, which is more longer,

SELECT (CONCAT_WS('-', 2012,1,1)) + INTERVAL (SELECT MONTH(CURDATE())) DAY AS date_left

• SQLFiddle Demo