My query:
SELECT *,
contacts.createdAt AS contactcreatedAt,
contacts.updatedAt AS contactupdatedAt,
bidresponses.itemid AS bidresponseitemid,
bidresponses.personid AS bidresponsepersonid,
SUM(tagsitems.quantity) AS totalquantity
FROM items
LEFT OUTER JOIN tagsitems ON items.id = tagsitems.itemid
LEFT OUTER JOIN itemscontacts ON items.id = itemscontacts.itemid
LEFT OUTER JOIN contacts ON itemscontacts.contactid = contacts.id
LEFT OUTER JOIN bidresponses ON items.id = bidresponses.itemid AND itemscontacts.personid = bidresponses.personid
LEFT OUTER JOIN bidtemplatefields ON bidresponses.bidtemplatefieldid = bidtemplatefields.id
WHERE ( (items.id = 70687 OR items.id = 70595) AND itemscontacts.relationship = 's' ) AND ( items.deletedAt IS NULL )
GROUP BY items.id, tagsitems.itemid, bidresponses.personid, bidresponses.bidtemplatefieldid
ORDER BY items.id ASC
Without the SUM() and GROUP BY clauses this query returns the desired results, minus the important totalquantity value.
The task that has got me stumped at the moment is constructing the GROUP BY clause so that it "ignores" NULL or missing values from bidresponses. The data is such that the results will be a mixed set of items - some have entries in the bidresponses table and some do not.
EDIT: I want the entries with NULL values in the bidresponses table to show up. With the current GROUP BY clause they are not. I should note that the only reason I am using the grouping at all is so that I can get the totalquantity value. Here is an example of the query output without the SUM() and GROUP BY clauses:
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
| id | itemtypeid | code | description | cost | unittypeid | projectid | companyid | createdAt | updatedAt | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate | responsedate | bidtemplateid | relationship | awarddate | assigndate | id | companyid | personid | companyidOwner | parentContactid | createdAt | updatedAt | firstName | lastName | company | email | bidtemplatefieldid | itemid | bidresponse | personid | id | bidtemplatefield | fieldtypeid | contactcreatedAt | contactupdatedAt | bidresponseitemid | bidresponsepersonid |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
| 70595 | 1 | NULL | HD Banners | NULL | NULL | 7 | 1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL | each | NULL | NULL | NULL | 70595 | 16 | 34789 | 2010-08-14 22:37:01 | NULL | 1 | s | NULL | NULL | 16 | NULL | NULL | 1 | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | sdf | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL |
| 70595 | 1 | NULL | HD Banners | NULL | NULL | 7 | 1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL | each | NULL | NULL | NULL | 70595 | 22 | 34794 | 2010-08-14 18:44:02 | NULL | 1 | s | NULL | NULL | 22 | NULL | 34794 | 1 | NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 | NULL | NULL | anewwwww | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-09 19:56:28 | 2010-08-10 13:55:03 | NULL | NULL |
| 70595 | 1 | NULL | HD Banners | NULL | NULL | 7 | 1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL | each | NULL | NULL | NULL | 70595 | 27 | 34797 | 2010-08-14 22:36:59 | NULL | 1 | s | NULL | NULL | 27 | NULL | NULL | 1 | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL |
| 70595 | 1 | NULL | HD Banners | NULL | NULL | 7 | 1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL | each | NULL | NULL | NULL | 70595 | 28 | 34798 | 2010-08-14 22:37:00 | NULL | 1 | s | NULL | NULL | 28 | NULL | NULL | 1 | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 12 | 70687 | NULL | 70687 | 16 | 34789 | 2010-08-14 22:37:01 | NULL | 1 | s | NULL | NULL | 16 | NULL | NULL | 1 | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | sdf | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 2 | 70687 | NULL | 70687 | 16 | 34789 | 2010-08-14 22:37:01 | NULL | 1 | s | NULL | NULL | 16 | NULL | NULL | 1 | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | sdf | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 12 | 70687 | NULL | 70687 | 27 | 34797 | 2010-08-14 22:36:59 | NULL | 1 | s | NULL | NULL | 27 | NULL | NULL | 1 | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 2 | 70687 | NULL | 70687 | 27 | 34797 | 2010-08-14 22:36:59 | NULL | 1 | s | NULL | NULL | 27 | NULL | NULL | 1 | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:11:52 | NULL | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 12 | 70687 | NULL | 70687 | 28 | 34798 | 2010-08-14 22:37:00 | NULL | 1 | s | NULL | NULL | 28 | NULL | NULL | 1 | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 2 | 70687 | NULL | 70687 | 28 | 34798 | 2010-08-14 22:37:00 | NULL | 1 | s | NULL | NULL | 28 | NULL | NULL | 1 | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL | [email protected] | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-10 19:18:27 | NULL | NULL | NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------------------+---------------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+
Here is an example of the query output with the SUM() and GROUP BY clauses:
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
| id | itemtypeid | code | description | cost | unittypeid | projectid | companyid | createdAt | updatedAt | deletedAt | unittype | tagid | itemid | quantity | itemid | contactid | personid | sentdate | responsedate | bidtemplateid | relationship | awarddate | assigndate | id | companyid | personid | companyidOwner | parentContactid | createdAt | updatedAt | firstName | lastName | company | email | bidtemplatefieldid | itemid | bidresponse | personid | id | bidtemplatefield | fieldtypeid | contactcreatedAt | contactupdatedAt | bidresponseitemid | bidresponsepersonid | totalquantity |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+
| 70595 | 1 | NULL | HD Banners | NULL | NULL | 7 | 1 | 2010-05-10 17:00:11 | 2010-08-14 18:57:41 | NULL | each | NULL | NULL | NULL | 70595 | 16 | 34789 | 2010-08-14 22:37:01 | NULL | 1 | s | NULL | NULL | 16 | NULL | NULL | 1 | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | sdf | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | NULL |
| 70687 | 1 | NULL | Editing and adding labels | NULL | NULL | 7 | 1 | 2010-05-15 07:26:33 | 2010-08-14 18:55:48 | NULL | each | 12 | 70687 | NULL | 70687 | 16 | 34789 | 2010-08-14 22:37:01 | NULL | 1 | s | NULL | NULL | 16 | NULL | NULL | 1 | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | sdf | [email protected] | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2010-08-05 18:40:01 | 2010-08-05 18:41:40 | NULL | NULL | NULL |
+-------+------------+------+---------------------------+------+------------+-----------+-----------+---------------------+---------------------+-----------+----------+-------+--------+----------+--------+-----------+----------+---------------------+--------------+---------------+--------------+-----------+------------+------+-----------+----------+----------------+-----------------+---------------------+---------------------+-----------+----------+---------+----------------+--------------------+--------+-------------+----------+------+------------------+-------------+---------------------+---------------------+-------------------+---------------------+---------------+