CODEWITHSUNDEEP
mysqlsql
user5175034
user5175034

Reputation:

Query With Both Join and Derived Table

I'm trying to add row counts to an existing query but am getting an error

unknown column 'pl.GroupNumber' in 'on clause'

I'm apparently going to answer this too in case it helps someone else as, after writing all this here, it occurred to me to reverse the FROM to:

*FROM (SELECT @row_number:=0) AS t, parts_listing pl *

which seemed to do the trick! Don't quite understand why it worked but it did.

SELECT DISTINCT 
    (@row_number:=@row_number + 1) AS RowNum,
    pl.ID,
    pn.ID AS SubID,
    IF(ListType,(SELECT MAX(ID) FROM parts_notes
    WHERE PageNo=429
        AND ListType IS NOT NULL AND SubPage IS NULL AND
        (BasePart IS NOT NULL AND Models IS NOT NULL
        AND (BasePart=pl.PartNo
        AND pl.Models LIKE CONCAT('%', Models ,'%')))
        OR  (BasePart IS NOT NULL AND Models IS NULL
        AND BasePart=pl.PartNo) ),NULL) AS SubMax,
    ListType,
    IndentText,
    BaseGroup,
    BaseName,
    GroupName,
    Title,
    `Name`,
    pl.Models,
    pl.PartNo,
    pn.PartNo AS SubPartNo,
    pl.Quantity,
    pn.Quantity AS SubQuantity,
    pn.Description AS SubDescription,
    ListType,
    Column_1,
    Column_2,
    Column_3,
    Column_4,
    Column_5,
    Column_6,
    Column_7,
    Column_8,
    COALESCE(pl.GroupNumber, pn.GroupNo) AS GroupNo,
    COALESCE(pl.Description, pn.Description) AS Description,
    COALESCE(pl.PageNo, pn.PageNo) AS PageNo,
    COALESCE(pl.SubPage, pn.SubPage) AS SubPage,
    COALESCE(pl.RevDate, pn.RevDate) AS RevDate,
    COALESCE(pl.Edition, pn.Edition) AS Edition
    FROM parts_listing pl, (SELECT @row_number:=0) AS t 
    LEFT JOIN parts_notes pn ON pl.GroupNumber=pn.GroupNo
    AND ((ListType < 5 AND (pl.PartNo=BasePart AND BasePart IS NOT NULL) OR BasePart IS NULL)
    OR ((ListType > 4) AND (pl.GroupNumber=pn.GroupNo OR pl.PartNo=BasePart)))
WHERE BaseGroup=30 AND pl.PageNo=429 AND (pl.SubPage IS NULL
    AND pn.SubPage IS NULL)
    AND (pl.PageNo = pn.PageNo OR pn.PageNo IS NULL)
    AND (pl.SubPage = pn.SubPage OR pn.SubPage IS NULL) 
ORDER BY pl.ID, pn.ID 
LIMIT 150;

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Do not use commas in the FROM clause, even when you intend CROSS JOIN. The scoping rules are not what you expect, which is why you get an unknown column.

I put parameter conditions on the end, so I would write this as:

FROM parts_listing pl LEFT JOIN 
     parts_notes pn 
     ON pl.GroupNumber = pn.GroupNo AND
        ((ListType < 5 AND
         (pl.PartNo = BasePart AND BasePart IS NOT NULL
         ) OR BasePart IS NULL
        ) OR
        ((ListType > 4) AND
         (pl.GroupNumber = pn.GroupNo OR pl.PartNo = BasePart)
         )
        ) CROSS JOIN
     (SELECT @row_number := 0) params

Upvotes: 0

Related Questions