How to cross join a table and use fields from the select statment in the where clause of the cross join

Unfortunately I cannot make this a procedure in this case. I’m setting the variables in the select statement and using them in my cross join. My Count(*) as Line Item is 0…

SELECT @p := `purchaseorder`.`PO` AS `PO`
      ,`purchaseorder`.`Customer PO`
      ,`customer`.`Customer`
      ,`work_order`.`Work Order`
      ,@l := `work_order`.`Line Order` AS `Line Order`
      ,`Line Item`.`Line Item`
FROM `work_order`
LEFT JOIN `purchaseorder`
    ON `purchaseorder`.`PO` = `work_order`.`PO`
LEFT JOIN `customer`
    ON `customer`.`RN` = `purchaseorder`.`Customer`
CROSS JOIN (SELECT COUNT(*) AS `Line Item`
            FROM `work_order`
            WHERE `work_order`.`PO` = @p
                AND `work_order`.`Line Order` <= @l
            ) `Line Item`
WHERE `purchaseorder`.`PO` IS NOT NULL
ORDER BY `purchaseorder`.`PO`,`work_order`.`Line Order`

What am I doing wrong?

Answer

SELECT `purchaseorder`.`Customer PO`
      ,`purchaseorder`.`PO` AS `PO`
      ,`a_work_order`.`Line Order`
      ,(SELECT COUNT(*) AS `Line Item`
        FROM `work_order` AS `b_work_order`
        WHERE `b_work_order`.`PO` = `purchaseorder`.`PO`
            AND `b_work_order`.`Line Order` <= `a_work_order`.`Line Order`
        ) AS `Line Item`
      ,`a_work_order`.`Work Order`
      ,`purchaseorder`.`Order Date`
      ,`customer`.`Customer`
      ,`a_work_order`.`Part`
      ,`a_work_order`.`Description`
      ,`a_work_order`.`Quantity`
      ,`a_work_order`.`Price Each`
      ,(`a_work_order`.`Price Each`*`a_work_order`.`Quantity`) AS Total
FROM `work_order` AS `a_work_order`
INNER JOIN `purchaseorder`
    ON `purchaseorder`.`PO` = `a_work_order`.`PO`
LEFT JOIN `customer`
    ON `customer`.`RN` = `purchaseorder`.`Customer`
ORDER BY `purchaseorder`.`Order Date`,`purchaseorder`.`PO`,`Line Item`