如何从一个表中选择2个字段，并将其与另一个表的1个字段进行比较 [英] How to SELECT 2 fields from one table and compare it on 1 field of the another table

问题描述

我使用的是 mysql / php / apache 。我有以下情况：

I'm using mysql/php/apache. I have the following situation:

2表，我需要比较一些信息，如果我使用INNER JOIN没有问题，但我在我的列有一个问题。

2 tables where I need to compare some information, it is no problem if I use INNER JOIN, but I have a problem in my columns.

表1 - > invoices_account

表2 - > invoices_payable

在我的 invoices_account 上有以下字段： id，categ，categ_name，code，name，active，essential

on my invoices_account I have this fields: id, categ, categ_name, code, name, active, essential

我的 invoices_payable 我有： id，..（所有我的字段）..，参考

invoices_payable中的字段引用的值与另一个表invoices_account中的categ和代码的值相同。它只在一个字段中连接。

the value of my field reference in invoices_payable is the same value of categ and code in the other table invoices_account. It was concatenated in a one field only.

现在，我只需要报告基本帐户，我不知道如何从一个表中选择2个字段，比较另一个表格的1个字段。

我希望在我的解释中清楚，但它是一种没有计划的情况对该项目。现在我不能修改我的数据库的结构，因为我有大量的数据里面。

I hope to be clear in my explanation.. but it is a kind of situation that was not planned on that project. Now I can't modify the structure of my database 'cause I have tons of data inside.

有人知道如何解决这个热的麻烦吗？
非常感谢任何人。

Does somebody have any idea how to solve this hot trouble? Very Thanks for anyone.

我的旧查询是这样的，但不工作，因为字段 categ 代码需要级联才能与引用进行比较。

My old query was something like this, but not working cause the field categ and code need to be concatenated to be compared with referencia.

SELECT SUM(ip.total) as total, ip.due_date, ip.status, ip.referencia 
FROM invoices_payable ip 
INNER JOIN invoices_account ON ip.referencia = invoices_account.code 
WHERE due_date BETWEEN '$dinov' and '$dfnov' 
AND invoices_payable.referencia = invoices_account.code 
AND invoices_payable.status ='paid' 
AND invoices_account.essential = 1

• WHERE $ dinov，$ dfnov是我的日期。 / li>
  
  • WHERE $dinov, $dfnov is my date.

  推荐答案

  SELECT categ_name, ... FROM invoices_account 
  JOIN invoices_payable 
  ON CONCAT(invoices_account.categ, invoices_account.code) = invoices_payable.reference
  

  这篇关于如何从一个表中选择2个字段，并将其与另一个表的1个字段进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！