CODEWITHSUNDEEP
mysqlsql
Yuriy Tigiev
Yuriy Tigiev

Reputation: 187

MySQL, CROSS JOIN and unknown column

I wrote mysql query which use subquery. I can't understand why mysql shows me 'Unknown column 'T.TICKETID' in 'where clause'' in subquery. For MSSQL it works without problem.

SELECT @PERIODTYPE := 'Y';


SELECT
  @PERIODTYPE,
  CASE 
    WHEN @PERIODTYPE = 'W' THEN DATE_FORMAT(R.DATENEW,'%u')
    WHEN @PERIODTYPE = 'M' THEN DATE_FORMAT(R.DATENEW,'%Y-%m')
    WHEN @PERIODTYPE = 'Y' THEN DATE_FORMAT(R.DATENEW,'%Y')
    ELSE DATE_FORMAT(R.DATENEW,'%Y-%m-%d') 
  END `Period`,
  DATE_FORMAT(MIN(R.DATENEW),'%Y-%m-%d') min_date,
  DATE_FORMAT(MAX(R.DATENEW),'%Y-%m-%d') max_date,
  COUNT(*) Transactions,
  SUM(SQ.`Sold Units`)  `Sold Units`,
  FORMAT(MAX(P.TOTAL), 2) `Largest Order`, 
  FORMAT(SUM(P.TOTAL), 2) `Total $ Sold`,
  FORMAT(SUM(TXL.AMOUNT), 2) `Total Tax $ Collected`
FROM RECEIPTS R INNER JOIN TAXLINES TXL ON R.ID = TXL.RECEIPT
                INNER JOIN TAXES TX ON TXL.TAXID = TX.ID 
                INNER JOIN TAXCATEGORIES TXC ON TX.CATEGORY = TXC.ID 
                INNER JOIN PAYMENTS P ON P.RECEIPT = R.ID
                INNER JOIN TICKETS T ON R.ID = T.ID  
                CROSS JOIN (SELECT SUM(TL.UNITS) 'Sold Units' FROM TICKETLINES TL WHERE T.TICKETID = TL.TICKET)  SQ
GROUP BY CASE 
    WHEN @PERIODTYPE = 'W' THEN DATE_FORMAT(R.DATENEW,'%u')
    WHEN @PERIODTYPE = 'M' THEN DATE_FORMAT(R.DATENEW,'%Y-%m')
    WHEN @PERIODTYPE = 'Y' THEN DATE_FORMAT(R.DATENEW,'%Y')
    ELSE DATE_FORMAT(R.DATENEW,'%Y-%m-%d')
  END
ORDER BY R.DATENEW

Update 1

I replaced table name (TICKETS) instead of alias (T). I got error. Please, see the screenshot. enter image description here

SELECT @PERIODTYPE := 'D';


SELECT
  @PERIODTYPE,
  CASE 
    WHEN @PERIODTYPE = 'W' THEN DATE_FORMAT(R.DATENEW,'%u')
    WHEN @PERIODTYPE = 'M' THEN DATE_FORMAT(R.DATENEW,'%Y-%m')
    WHEN @PERIODTYPE = 'Y' THEN DATE_FORMAT(R.DATENEW,'%Y')
    ELSE DATE_FORMAT(R.DATENEW,'%Y-%m-%d') 
  END `Period`,
  DATE_FORMAT(MIN(R.DATENEW),'%Y-%m-%d') min_date,
  DATE_FORMAT(MAX(R.DATENEW),'%Y-%m-%d') max_date,
  COUNT(*) Transactions,
  FORMAT(SUM(IFNULL(SQ.`Sold Units`,0)),2)  `Sold Units`,
  FORMAT(MAX(P.TOTAL), 2) `Largest Order`, 
  FORMAT(SUM(P.TOTAL), 2) `Total $ Sold`,
  FORMAT(SUM(TXL.AMOUNT), 2) `Total Tax $ Collected`
FROM RECEIPTS R INNER JOIN TAXLINES TXL ON R.ID = TXL.RECEIPT
                INNER JOIN TAXES TX ON TXL.TAXID = TX.ID 
                INNER JOIN TAXCATEGORIES TXC ON TX.CATEGORY = TXC.ID 
                INNER JOIN PAYMENTS P ON P.RECEIPT = R.ID
                INNER JOIN TICKETS ON R.ID = TICKETS.ID 
--                LEFT JOIN (SELECT TL.TICKET, SUM(TL.UNITS) 'Sold Units' FROM TICKETLINES TL  GROUP BY TL.TICKET)  SQ ON T.ID = SQ.TICKET
                CROSS JOIN (SELECT SUM(TL.UNITS) 'Sold Units' FROM TICKETLINES TL WHERE  TICKETS.ID = SQ.TICKET)  SQ 

GROUP BY CASE 
    WHEN @PERIODTYPE = 'W' THEN DATE_FORMAT(R.DATENEW,'%u')
    WHEN @PERIODTYPE = 'M' THEN DATE_FORMAT(R.DATENEW,'%Y-%m')
    WHEN @PERIODTYPE = 'Y' THEN DATE_FORMAT(R.DATENEW,'%Y')
    ELSE DATE_FORMAT(R.DATENEW,'%Y-%m-%d')
  END
ORDER BY R.DATENEW

Upvotes: 0

Views: 404

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

The query don't work in mysql beacuse mysql can't use an alias declared ad an upper level respect the subquery ..

in your subquery in Cross Join 

CROSS JOIN (SELECT SUM(TL.UNITS) 'Sold Units' 
         FROM TICKETLINES TL WHERE T.TICKETID = TL.TICKET)  SQ

You use T.TICKETID

The alias T 

  INNER JOIN TICKETS T ON R.ID = T.ID

Is declare in a place not "accessible" by the scope of the subquery ..

Then if is possible i suggest you of build a subquery that non refer to T alias .. Try (if is possible ) building an equivalent subquery wihout alias

Upvotes: 1

Related Questions