So, I have a database that I am creating. It stores information about families and each family member. It then uses those records to associate invoices to either a family or family member.
My dilemma is that I need to list all of these invoices to a page under the families record i.e. create a list of invoices associated to either the family itself or an individual family member.
Table Structure
invoices
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
families
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
clients (family members)
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
My current SQL looks like:
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
But what I need a query that checks the client_type column and if it equals 1 it needs to look in the clients table BUT it needs to look for members of the same family, identified by the id row in the families table
SOLUTION
Ok, so after much, much (much) screwing around and a little research. It appears that @cupid was correct (Although very brief in his answer).
And I will explain the solution better (in hope that this will help someone later).
The UNION option in MySQL (and most likely other SQL) allows you to combine the result sets of two (or more) SELECT queries, into one result set. This is extremely helpful if you have similar data, in separate tables that you may want to select easily and process as one request. Also helpful (in my case) for pagination, by allowing you to utilise SQL's LIMIT option.
One thing to take into consideration is that, the UNION syntax uses the columns from the first SELECT statement as the column names for all following queries, also you need to make sure that you have the same amount of columns selected in all queries for this to work.
(
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
)
