CODEWITHSUNDEEP
mysqlsqlselect
Jonathan M
Jonathan M

Reputation: 17451

How to access column from other table in calculation?

Sorry for the title. Not really sure how to word it. Edits appreciated.

I have a table, bin_content, which has a column, LotId that I'm trying to do a calculation on. The calculation is a bit complex, but I've boiled it down to the part that is failing in the sql below:

SELECT bc.LotId, bc.ModelId, x.datecode FROM (
    SELECT
        LEFT(bc.LotId,4) as datecode
) x, bin_content bc WHERE bc.Quantity <> 0;

When I run this, I'm getting:

SQL Error (1109): Unknown table 'bc' in field list.

The error is referring to the reference on the third line of code:

        LEFT(bc.LotId,4) as datecode

I'm wanting to access the value of LotId in the current record. What's the right way to do this?

EDIT:

Here's the actual calculation:

SELECT bc.LotId, bc.ModelId, x.monthAge FROM (
    SELECT
        @reportdate := CURDATE(),
        @datecode := LEFT(bc.LotId,4),
        @dcyear := CONCAT("20",LEFT(@datecode,2)),
        @dcweek := SUBSTRING(@datecode,3,2),
        @dcyearstart := CONCAT(@dcyear,'-01-01'),
        @firstWeekLength := MOD((8-DAYOFWEEK(@dcyearstart)),7),
        @builddate := ADDDATE(@dcyearstart,IF(@dcweek = 1, 0, @firstWeekLength + (7*(@dcweek-2)))),
        @partialmonth := (DAY(@reportdate) - DAY(@builddate))/31,
        @monthAge := ((YEAR(@reportdate) - @dcyear) * 12) + (MONTH(@reportdate) - MONTH(@builddate)) + @partialmonth as monthAge
) x, bin_content bc WHERE bc.Quantity <> 0;

The line below is the problem child:

        @datecode := LEFT(bc.LotId,4),

If I can get that line to return a value, the rest works. The basic idea is that the bc.LotId has a date code in the first four digits (YYWW, where YY=year, and WW=week number). I'm calculating the number of months that have passed since that date code.

Upvotes: 2

Views: 51

Answers (3)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

Hope this may help:

SELECT bc.LotId, bc.ModelId, x.datecode FROM (
    SELECT LEFT(bc2.LotId,4) as datecode
    From bin_content bc2 
    WHERE bc2.LotId = bc.LotId
) x, bin_content bc WHERE bc.Quantity <> 0;

Update : I think why this will not work in you case?

SELECT bc.LotId, bc.ModelId, LEFT(bc.LotId,4) as datecode 
From bin_content bc WHERE bc.Quantity <> 0;

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16691

The issue is because bin_content is not in the scope of your subquery.

Removing a lot of the code, you have a skeleton like this:

SELECT stuff
FROM(
   SELECT stuff
) x, bin_content bc...

There is no FROM clause inside your inner select query, so bc cannot be referenced. The query is complex, so I'm not sure if making it like this will work:

SELECT stuff
FROM(
   SELECT stuff
   FROM bin_content bc
) x, bin_content bc...

but the issue is definitely as a result of bc not being in the proper scope.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311188

Edited answer after the question was updated:

Since you only rely on a single row for this calculation, you can move all the columns to the inner query and select from there:

SELECT  x.LotId, 
        x.ModelId, 
        x.monthAge 
FROM    (SELECT  bc.LotId, 
                 bc.ModelId,
                 @reportdate := CURDATE(),
                 @datecode := LEFT(bc.LotId,4),
                 @dcyear := CONCAT("20",LEFT(@datecode,2)),
                 @dcweek := SUBSTRING(@datecode,3,2),
                 @dcyearstart := CONCAT(@dcyear,'-01-01'),
                 @firstWeekLength := MOD((8-DAYOFWEEK(@dcyearstart)),7),
                 @builddate := ADDDATE(@dcyearstart,IF(@dcweek = 1, 0, @firstWeekLength + (7*(@dcweek-2)))),
                 @partialmonth := (DAY(@reportdate) - DAY(@builddate))/31,
                 @monthAge := ((YEAR(@reportdate) - @dcyear) * 12) + (MONTH(@reportdate) - MONTH(@builddate)) + @partialmonth as monthAge
         FROM    bin_content bc
         WHERE   bc.Quantity <> 0) x

Upvotes: 2

Related Questions