Hoping someone can help me out. I have 5 tables, simplified examples below:
catalog_items: Stores information about items in a catalog.
CREATE TABLE catalog_items (
item_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
create_date DATETIME NOT NULL,
valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (item_id)
);
users: Stores user information.
CREATE TABLE users (
user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (user_id)
);
user_contribution_types: Users can contribute items to the catalog to earn credits, this table describes what type of contribution it is and how much they earn.
CREATE TABLE user_contribution_types (
type_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
description TEXT NOT NULL,
credits DECIMAL(5,2) UNSIGNED NOT NULL,
valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (type_id)
);
user_contributions: Table to relate all of the above (which user contributed x item).
CREATE TABLE user_contributions (
user_id INTEGER UNSIGNED NOT NULL,
item_id INTEGER UNSIGNED NOT NULL,
type_id INTEGER UNSIGNED NOT NULL,
create_date DATETIME NOT NULL,
valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
FOREIGN KEY (user_id)
REFERENCES users(user_id),
FOREIGN KEY (item_id)
REFERENCES catalog_items(item_id),
FOREIGN KEY (type_id)
REFERENCES user_contribution_types(type_id)
);
pending_items: All contributions must be approved, so they're stored in a temporary table. These entries might be coming from new users without an account yet, which is why there is only an email and not a user_id.
CREATE TABLE pending_items (
pending_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
create_date DATETIME NOT NULL,
valid TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
PRIMARY KEY (pending_id)
);
Question
What I am attempting to do is select both approved and pending items from the catalog; however, the best I've come up with so far is to split them up:
Select only approved contributions:
SELECT a.item_id, c.title, a.create_date, b.description, b.credits
FROM user_contributions a, user_contribution_types b, catalog_items c
WHERE a.type_id = b.type_id AND a.item_id = c.item_id
AND a.valid = TRUE AND b.valid = TRUE AND c.valid = TRUE
ORDER BY c.create_date DESC LIMIT 0, 10
Select only pending contributions:
SELECT pending_id, title, create_date FROM pending_items WHERE email = '[email protected]' AND valid = TRUE
ORDER BY create_date DESC LIMIT 0, 10
Is there a way to combine the above 2 queries to get a result set like the following, where missing fields simply display as NULL?
| item_or_pending_id | title | create_date | description | credits | approved |
| ------------------ | ----- | ------------------- | ----------- | ------- | -------- |
| 14 | test4 | 2017-04-08 04:41:21 | bar | 1.00 | true |
| 14 | test3 | 2017-04-06 01:23:45 | NULL | NULL | false |
| 11 | test2 | 2017-02-03 12:30:00 | NULL | NULL | false |
| 9 | test1 | 2017-01-25 05:16:18 | foo | 1.00 | true |
