Problem joining on concatenated data

[nichemtktg]

I get this error: Column not found: 1054 Unknown column ‘t1.item2’ in ‘on clause’

changing

AS item2
to

AS t1.item2

doesn’t help.

How do I get the code to see item2 as a t1 column?

Here’s my code:
SELECT
LEFT(datetime,6) AS date,
t1.itemandsize,
t1.pkg_quantity,
CONCAT( t1.itemandsize, t1.pkg_quantity) AS item2
FROM
nconnectz
AS
t1
JOIN
(
SELECT
MAX(date) AS maxdate,
itemandsize,
pkg_quantity,
CONCAT(itemandsize,pkg_quantity) AS item3
FROM
nconnectz
GROUP BY
item3
)
AS
t2
ON
t1.item2 = t2.item3
AND
t1.date = t2.maxdate


[r937]

the reason you’re getting that error is because t1 doesn’t contain a column called item2

you’re defining item2 as a column alias in the SELECT clause, and you cannot refer to it in the ON clause, especially not with t1 qualifier

solution – replace the column alias in the ON clause with the definition of the column alias

SELECT LEFT(datetime,6) AS date , t1.itemandsize , t1.pkg_quantity , CONCAT( t1.itemandsize, t1.pkg_quantity) AS item2 FROM nconnectz AS t1 JOIN ( SELECT MAX(date) AS maxdate , itemandsize , pkg_quantity , CONCAT(itemandsize,pkg_quantity) AS item3 FROM nconnectz GROUP BY item3 ) AS t2 ON CONCAT( t1.itemandsize, t1.pkg_quantity) = t2.item3 AND t1.date = t2.maxdate

p.s. is there any reason why you’re concatenating those columns for the join? couldn’t you just join the pair of columns?

SELECT LEFT(datetime,6) AS date , t1.itemandsize , t1.pkg_quantity FROM nconnectz AS t1 JOIN ( SELECT MAX(date) AS maxdate , itemandsize , pkg_quantity FROM nconnectz GROUP BY itemandsize , pkg_quantity ) AS t2 ON t1.itemandsize = t2.itemandsize AND t1.pkg_quantity = t2.pkg_quantity AND t1.date = t2.maxdate

[r937]

oh, one other comment…

this –

LEFT(datetime,6) AS date

is truly a hack, you should never rely on implicit conversion of a datetime value to a formatted character string…

use the DATE function instead, that’s what it’s for

DATE(datetime) AS date

[nichemtktg]

r937, I always feel privileged to get help from you.

This is a thought experiment for cold rainy Nebraska evening. Just decided to challenge myself to join on a concatenated data.

This turned out to be a great learning experience thanks to you!

You’re absolutely right to point out the hack. Bad habit I acquired from programming in the mid 70’s.

Thanks, as always, for your help!

niche