因此，我有一个正在创建的数据库。它存储有关家庭和每个家庭成员的信息。然后，它将使用这些记录将发票与家庭或家庭成员相关联。

我的困境是我需要在家庭记录下的页面上列出所有这些发票，即创建与家庭本身或单个家庭成员相关的发票列表。

表结构

发票

id | date_entered | invoice_date | invoice_number | invoice_amount | client_type | unique_id | supplier_type | supplier_id | category_id | childcare_hours
---+--------------+--------------+----------------+----------------+-------------+-----------+---------------+-------------+-------------+----------------
1  | 1411098397   | 1411048800   | 123            | 0.01           | 0           | 137       | 0             | 139         | 5           | NULL

家庭

id |  ufi     |   last_name     | address_1   | address_2 | city_id | phone | mobile | email | f_d_worker_1 | f_d_worker_2 | status_id | trans_date | entry_date | exit_date | eligible_date | active_date | lga_loc_id | facs_loc_id | ind_status_id | referral_id | active_status | comm_org_id | notes
---+----------+-----------------+-------------+-----------+---------+-------+--------+-------+--------------+--------------+-----------+------------+------------+-----------+---------------+-------------+------------+-------------+---------------+-------------+---------------+-------------+-------
1  | 1-XEWUDZ | Forsyth - Ennis | Skinner St. | NULL      | NULL    | NULL  | NULL   | NULL  | 13           | NULL         | 1         | NULL       | 1341324000 | NULL      | 1341842400    | 1342620000  | 7          | 1           | 3             | NULL        | 1             | 1           | NULL

客户（家庭成员）

id |   upi    | last_name | first_name | birthdate  | sex | phone | mobile | email | indig_status_id | referral_id | relationship_id | preschool_id | family_id | notes
---+----------+-----------+------------+------------+-----+-------+--------+-------+-----------------+-------------+-----------------+--------------+-----------+------
 1 | 1-XFCBBP | Ennis     | Jason      | 20/09/1996 | 1   | NULL  | NULL   | NULL  | 3               | NULL        | NULL            | NULL         | 1         | NULL

我当前的SQL看起来像：

SELECT `invoices`.`id`, `invoices`.`date_entered`, `invoices`.`invoice_date`, `invoices`.`invoice_number`, `invoices`.`invoice_amount`, `invoices`.`client_type`, `invoices`.`unique_id`, `unique1`.`ufi`, `unique2`.`upi`, `unique1`.`last_name`, `invoices`.`supplier_type`, `invoices`.`supplier_id`, `suppliers`.`name`,  `invoices`.`category_id`, `cat1`.`name`, `cat2`.`name`, `invoices`.`childcare_hours` 
FROM `invoices` 
LEFT OUTER JOIN `suppliers` ON `suppliers`.`id` = `invoices`.`supplier_id` 
LEFT OUTER JOIN `categories` cat1 ON `cat1`.`id` = `invoices`.`category_id` 
LEFT OUTER JOIN `preschool_types` cat2 ON `cat2`.`id` = `invoices`.`category_id` 
LEFT OUTER JOIN `families` unique1 ON `unique1`.`id` = `invoices`.`unique_id` 
LEFT OUTER JOIN `clients` unique2 ON `unique2`.`id` = `invoices`.`unique_id` 
WHERE (`invoices`.`unique_id` = ? AND `unique1`.`ufi` = ?) LIMIT 0, 10

但是我需要一个查询来检查该client_type列，如果它等于1，它需要在clients表中查找，但它需要查找由families表中的id行标识的同一个家族的成员

解决方案

好吧，经过很多很多研究和研究之后。看来@cupid是正确的（尽管他的回答很简短）。

我会更好地解释该解决方案（希望以后对您有所帮助）。

MySQL（以及最可能的其他SQL）中的UNION选项使您可以将两个（或多个）SELECT查询的结果集组合为一个结果集。如果您在单独的表中有相似的数据，这可能非常有帮助，您可能希望轻松选择这些表并作为一个请求进行处理。通过允许您使用SQL的LIMIT选项，对分页也很有帮助（对我而言）。

要考虑的一件事是，UNION语法使用第一个SELECT语句中的列作为所有后续查询的列名，还需要确保在所有查询中选择的列数相同，以达到此目的。工作。

(
    SELECT 
        `invoices`.`id`, 
        `invoices`.`date_entered`, 
        `invoices`.`invoice_date`, 
        `invoices`.`invoice_number`, 
        `invoices`.`invoice_amount`, 
        `invoices`.`client_type`, 
        `invoices`.`unique_id`, 
        `clients`.`upi`, 
        `clients`.`last_name`, 
        `clients`.`family_id`,
        `invoices`.`supplier_type`, 
        `invoices`.`supplier_id`, 
        `suppliers`.`name`, 
        `invoices`.`category_id`, 
        `cat1`.`name`, 
        `cat2`.`name`, 
        `invoices`.`childcare_hours` 
    FROM
    (
        `invoices` 
            LEFT OUTER JOIN `suppliers` ON `suppliers`.`id` = `invoices`.`supplier_id`
            LEFT OUTER JOIN `categories` cat1 ON `cat1`.`id` = `invoices`.`category_id`
            LEFT OUTER JOIN `preschool_types` cat2 ON `cat2`.`id` = `invoices`.`category_id`
            LEFT OUTER JOIN `clients` ON `clients`.`id` = `invoices`.`unique_id`)
        WHERE 
            `clients`.`family_id` = 47 AND `invoices`.`client_type` = 1
    )
UNION
( 
    SELECT 
        `invoices`.`id`,
        `invoices`.`date_entered`, 
        `invoices`.`invoice_date`, 
        `invoices`.`invoice_number`, 
        `invoices`.`invoice_amount`, 
        `invoices`.`client_type`, 
        `invoices`.`unique_id`, 
        `families`.`ufi`, 
        `families`.`last_name`,
        `families`.`id`,
        `invoices`.`supplier_type`, 
        `invoices`.`supplier_id`, 
        `suppliers`.`name`, 
        `invoices`.`category_id`, 
        `cat1`.`name`, 
        `cat2`.`name`, 
        `invoices`.`childcare_hours` 
    FROM `invoices`
        LEFT OUTER JOIN `suppliers` ON `suppliers`.`id` = `invoices`.`supplier_id` 
        LEFT OUTER JOIN `categories` cat1 ON `cat1`.`id` = `invoices`.`category_id`
        LEFT OUTER JOIN `preschool_types` cat2 ON `cat2`.`id` = `invoices`.`category_id`
        LEFT OUTER JOIN `families` ON `families`.`id` = `invoices`.`unique_id`
    WHERE 
        `invoices`.`unique_id` = 47 AND `invoices`.`client_type` = 0
)