如何使用FROM子句中的多个表和WHERE子句中的一些搜索条件来计算一个表中的行？

I have a QUERY which is like

我有一个QUERY就像

SELECT COUNT(*) as cnt 
    FROM tbl_docatrtypegroupdoctype, 
         tbl_doctype, 
         tbl_docatrtypegroup 
    WHERE 1=1  
          AND 
         (tbl_doctype.doctype_name like '%Payment%' 
            OR tbl_doctype.doctype_name like'% Payment' 
            OR tbl_doctype.doctype_name like ' Payment%' ) 
    LIMIT 1

Now in the above query I need to count the number of records in table "tbl_docatrtypegroupdoctype" under the conditons given in where clause, whenever i execute the query, I get 77 count, but actual count in DB is 12.

现在在上面的查询中我需要计算where子句中给出的条件下表“tbl_docatrtypegroupdoctype”中的记录数,每当我执行查询时,我得到77计数,但DB中的实际计数是12。

What could be the problem with this query and how can I rectify it?

这个查询可能有什么问题,如何纠正?

Ant help will be appriciated

Ant帮助将受到关注

Thanks

2 个解决方案

#1

You need to specify your join conditions. What happens if you don't is a cross product which is not what you want.

您需要指定您的加入条件。如果你不这样做会发生什么,而不是你想要的交叉产品。

 SELECT COUNT(*) as cnt 
FROM tbl_docatrtypegroupdoctype JOIN  
     tbl_doctype on (THE CONDITION) JOIN 
     tbl_docatrtypegroup on (THE CONDITION)

Alternatively the JOIN conditions can be spefified in the WHERE clause.

或者,可以在WHERE子句中指定JOIN条件。

In the where clause:

在where子句中:

  WHERE table1.field1 = table2.field2 AND table2.field3 = table3.field4

The fields that you join on must be semantically related in some way of course.

您加入的字段必须在某种程度上与语义相关。

#2

You need to apply all join-conditions between the three tables.

您需要在三个表之间应用所有连接条件。

#1