Comparing data using year in query

I am missing something about your request; the displayed SQL text cannot give a result.

There is no source for the column in the SELECT list items

, and there is no table aliased like cs

. (It looks like the expression in the SELECT list should be cu.amount

In addition, the returned results look exactly as we expected. Every row returned from year=2

is matched against every row returned from year=1

. If year=1

there were three lines for year=1

and two lines for year=2

, we would get six lines back ... each line for year=1

"matched" each line for year=2

.

If (cid, year)

tuple was UNIQUE in Cost

then this query will return a result similar to expected.

  SELECT b.items
       , IFNULL(ps.amount, '0') AS pastCost
       , IFNULL(cu.amount, '0') AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN `Cost` ps
      ON ps.cid  = b.cid 
     AND ps.Year = 1
    LEFT
    JOIN `Cost` cu 
      ON cu.cid  = b.cid 
     AND cu.Year = 2

      

        
        
        
      

    

Since it is (cid, year)

not unique, you need an extra column to "match" one row for year=1

with one row for year=2

.

Without any other column in the table, we can use the inline view to generate the value. I can illustrate how we can get MySQL to return a result set like the one you are showing in one way that can be done, but I don't think this is really a solution to whatever problem you are trying to solve:

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

      

        
        
        
      

    

A query like this will return a result set similar to the one you expect. But then again, I strongly suspect that these are not actually the results you are really looking for.

EDIT

OP leaves a comment with a vaguely vague message about the observed behavior: "the above solution doesn't work "

Ok then check it out ... create a SQL Fiddle with some tables so we can test the query ...

The SQL script is here http://sqlfiddle.com/#!9/e3d7e/1

create table `Cost Items` (cid int unsigned, items varchar(5));
insert into `Cost Items` (cid, items) values (1,'A'),(2,'B');
create table `Cost` (cid int unsigned, amount int, year int);
insert into `Cost` (cid, amount, year) VALUES (1,10,1),(1,20,2),(1,30,1);

      

        
        
        
      

    

And when we run the request, we get a syntax error. Where there is no list in the SELECT list, it is easy enough to commit.

  SELECT b.items
       , IFNULL(MAX(IF(a.year=1,a.amount,NULL)),0) AS pastCost
       , IFNULL(MAX(IF(a.year=2,a.amount,NULL)),0) AS currentCost
    FROM `Cost Items` b
    LEFT
    JOIN ( SELECT @rn := IF(c.cid=@p_cid AND c.year=@p_year,@rn+1,1) AS `rn` 
                , @p_cid := c.cid AS `cid`
                , @p_year := c.year AS `year`
                , c.amount
             FROM (SELECT @p_cid := NULL, @p_year := NULL, @rn := 0) i
             JOIN `Cost` c
               ON c.year IN (1,2)
            ORDER BY c.cid, c.year, c.amount
         ) a
      ON a.cid = b.cid
   GROUP
      BY b.cid
       , a.rn

      

        
        
        
      

    

Return:

  items   pastCost  currentCost  
  ------  --------  -----------
  A             10           20
  A             30            0
  B              0            0