WHERE子句中的MySQL INNER JOIN错误

I have problem with query in this example:

我在这个例子中遇到查询问题:

 SELECT pm.name,
       pm.pre_id,
       pm.pgs_id,
       pm.link,
       pm.del,
       pm.maz_kaina,
       pm.int_kaina,
       pm.d1_kaina,
       pm.d2_kaina,
       pm.d3_kaina,
       pm.pvm,
       pm.vnt,
       pm.gamintojas,
       pm.akcija,
       pm.akc_kaina
FROM   `prekes_main` AS pm
       INNER JOIN (SELECT charak_pre.pr_id
                   FROM   charak_pre
                   WHERE  charak_pre.cha_id = 206
                          AND charak_pre.reiksme = 'Parfumuotas vanduo'
                          AND charak_pre.pr_id = pm.pre_id) AS reikiamos_pre0
               ON reikiamos_pre0.pr_id = pm.pre_id
WHERE  pm.pgs_id = '2431'
ORDER  BY pm.del DESC,
          int_kaina ASC
LIMIT  0, 18  

I have problem in INNER JOIN when I try execute this. This is error which I got:

当我尝试执行此操作时,我在INNER JOIN中遇到问题。这是我得到的错误:

SQL Error (1054): Unknown column 'pm.pr_id' in WHERE clause.

Location where the error occurs is: and charak_pre.pr_id = pm.pr_id. With this I want to select row from charak_pre table witch equals prekes_main row by keys charak_pre.pr_id and prekes_main.pre_id.

发生错误的位置是:和charak_pre.pr_id = pm.pr_id。有了这个,我想从charak_pre表中选择行等于prekes_main行按键charak_pre.pr_id和prekes_main.pre_id。

2 个解决方案

#1

I would write it without the subquery, seems like a inefficient thing to do, but I haven't tested it. Do a normal join instead:

我会在没有子查询的情况下编写它,看起来像是一个低效的事情,但我没有测试过它。改为正常加入:

SELECT  pm.name, 
        pm.pre_id, 
        pm.pgs_id, 
        pm.link, 
        pm.del, 
        pm.maz_kaina, 
        pm.int_kaina, 
        pm.d1_kaina, 
        pm.d2_kaina, 
        pm.d3_kaina, 
        pm.pvm, 
        pm.vnt, 
        pm.gamintojas, 
        pm.akcija, 
        pm.akc_kaina
FROM    `prekes_main` as pm
        INNER JOIN charak_pre charak_pre  as reikiamos_pre0 
            ON reikiamos_pre0.pr_id = pm.pre_id AND charak_pre.cha_id = 206 and 
                    charak_pre.reiksme = 'Parfumuotas vanduo'
WHERE   pm.pgs_id = '2431' 
ORDER   BY pm.del DESC, 
        int_kaina ASC 
LIMIT 0,18

#2

Actually you can do it without a subquery because you haven't perform any aggregation on the following rows. But instead directly join it with table charak_pre and put the other condition on the WHERE clause.

实际上,您可以在没有子查询的情况下执行此操作,因为您没有对以下行执行任何聚合。但是直接将其与表charak_pre连接并将其他条件放在WHERE子句上。

SELECT  DISTINCT pm.*
FROM    prekes_main as pm
        INNER JOIN charak_pre
           ON charak_pre.pr_id = pm.pre_id
WHERE   pm.pgs_id = '2431' AND 
        charak_pre.cha_id = 206 AND 
        charak_pre.reiksme = 'Parfumuotas vanduo'
ORDER   BY pm.del DESC, int_kaina ASC 
LIMIT   0, 18