asked on

mysql inner join sentence

Dear Experts,
I use PHP and mysql,
I want to select the details and the total from my siparis list with the one sql sentence however, there is an error.
Although there is a coloumn named _key it gives the below error.
what do you suggest I should do?

#1054 - Unknown column 'g._key' in 'on clause'

SELECT
t._key
, t.uid
, t.bayiadi
, t.adet
, t.toplamtutar
FROM siparis t
INNER JOIN (
SELECT
_key
, uid
, bayiadi
, SUM( toplamtutar ) AS Total
FROM siparis g
GROUP BY
_key
, uid
, bayiadi
) sq ON t._key = g._key
AND t.uid = g.uid
AND t.bayiadi = g.bayiadi
WHERE MONTH( t.tarih ) = 12
AND YEAR( t.tarih ) = 2018
AND t.uid = 'xxxxxxxx'
AND t.gecerli = '1'
ORDER BY
t. _key DESC

Ryan Chong

try bracket the field by adding ` and see if it works?

`_key`

ASKER

I wrote it like this

ON t._key =g.`_key`
AND t.uid = g.`_uid`
AND t.bayiadi = g.`bayiadi` but it didn't wor.

the error message is : #1054 - Unknown column 'g._key' in 'on clause'

PortletPaul

SELECT
    t._key
  , t.uid
  , t.bayiadi
  , t.adet
  , t.toplamtutar
FROM siparis t
INNER JOIN (
        SELECT
            _key
          , uid
          , bayiadi
          , SUM( toplamtutar ) AS Total
        FROM siparis g
        GROUP BY
            _key
          , uid
          , bayiadi
    ) sq ON t._key = sq._key
    AND t.uid = sq.uid
    AND t.bayiadi = sq.bayiadi
WHERE MONTH( t.tarih ) = 12
AND YEAR( t.tarih ) = 2018
AND t.uid = 'xxxxxxxx'
AND t.gecerli = '1'
ORDER BY
    t._key DESC

Open in new window

the subquery has an alias of SQ

[edit] you should stop using year() and month() to select that data, this is a slow method as it CANNOT use any index on that date column. I have discussed this in an earlier question.

Ryan Chong

try change:

) sq ON t._key = g._key

to:

) sq ON t._key = sq._key

PortletPaul

from (non-sargable)

WHERE MONTH( t.tarih ) = 12
AND YEAR( t.tarih ) = 2018

to (sargable):

WHERE t.tarih >= '2018-12-01'
AND t.tarih < '2019-01-01'

ASKER

Dear PortletPaul and Ryan Chong,

it works, but this time I can't see the Total column. SUM( toplamtutar ) AS Total
thank you

PortletPaul

because you don't include it in the top select clause

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.