MariaDb Use Column In primary Query in sub Query

Question

I have a table that has store names, product codes and prices. I need to pull the minimum price of a product and display a list of the product codes with their minimum price, where it all falls down is i need to display the store name that has the minimum price. to do this i need to use the minimum price and product code i get in the primary query and pass it to a sub query to get the store name but do not know how to do this.

SELECT 
    `Code`, 
    `wholsalerName`.`wholesaler`,
    min(round(`Price`,2))  as 'minPriceCol'
FROM `Listings_Lines` as listingLines
left join (
    select 
        `Code`, 
        `wholesaler` 
    from `Listings_Lines` 
    WHERE Price = listingLines.`minPriceCol`) as wholsalerName 
    on wholsalerName.Code = `Listings_Lines`.`Code`
WHERE `Listings_Lines`.`wholesaler` <> 'Pharmacon' and `Price` > 0
group BY `code`

this gives me an unknow column error

#1054 - Unknown column 'listingLines.minPriceCol' in 'where clause'

This question is similar to: Using an Alias in a WHERE clause. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — A.Steer
– A.Steer, Commented Jun 25 at 9:17
you don't have a field called listingLines.minPriceCol in your data, you have a min function that you have given an alias of minPriceCol, you cannot use this alias in the where caluse, and would either need to put the whole element into a subquery or use a having clause — A.Steer
– A.Steer, Commented Jun 25 at 9:19
Clearly one can expect that this question is a duplicate and that its zillionth asking is not useful. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. How much research effort is expected of Stack Overflow users? tour How to Ask Help center Reflect research in posts. Please in code questions give a minimal reproducible example. — philipxy
– philipxy, Commented Jun 25 at 10:00
Debug questions require a minimal reproducible example--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy
– philipxy, Commented Jun 25 at 10:09

philipxy · Accepted Answer · 2025-06-25 10:24:46Z

2

SELECT 
    ll.Code, 
    ll.wholesaler,
    min_prices.minPriceCol
FROM 
    `Listings_Lines` AS ll
JOIN (
    SELECT 
        Code, 
        MIN(ROUND(Price, 2)) AS minPriceCol
    FROM 
        `Listings_Lines`
    WHERE 
        wholesaler <> 'Pharmacon' AND Price > 0
    GROUP BY 
        Code
) AS min_prices 
ON ll.Code = min_prices.Code AND ROUND(ll.Price, 2) = min_prices.minPriceCol
WHERE 
    ll.wholesaler <> 'Pharmacon' AND ll.Price > 0;

edited Jun 25 at 10:24

philipxy

15.2k6 gold badges44 silver badges102 bronze badges

answered Jun 25 at 9:17

benni0108

1301 gold badge3 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Jun 25 at 9:27

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

MariaDb Use Column In primary Query in sub Query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related