Code: Select all
CREATE TABLE users (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
firstname VARCHAR(30) NULL,
lastname VARCHAR(50) NULL,
address TEXT NULL,
phone VARCHAR(20) NULL,
email VARCHAR(50) NULL,
pwd VARCHAR(64) NULL,
active INTEGER UNSIGNED NULL,
PRIMARY KEY(id)
);
CREATE TABLE greetings (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
categories_id INTEGER UNSIGNED NOT NULL,
users_id INTEGER UNSIGNED NOT NULL,
time_stamp INT NULL,
public TINYINT UNSIGNED NULL DEFAULT 1,
template INTEGER UNSIGNED NULL,
message TEXT NULL,
PRIMARY KEY(id, categories_id, users_id),
INDEX greetings_FKIndex1(categories_id),
INDEX greetings_FKIndex2(users_id)
);
CREATE TABLE recipients (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
users_id INTEGER UNSIGNED NOT NULL,
name Varchar(45) NULL,
email Varchar(45) NULL,
PRIMARY KEY(id)
);
CREATE TABLE greetings_to_recipients (
greeting_id INTEGER UNSIGNED NOT NULL,
recipient_id INTEGER UNSIGNED NULL
);How would I go about selecting a greeting, along with the username (this part is simple), along with the (possibly many) recipients?
I'm currently using:
Code: Select all
SELECT
gt.id,
gt.title,
gt.users_id,
gt.time_stamp,
gt.message,
ut.username
FROM
greetings AS gt
LEFT JOIN users AS ut ON gt.users_id = ut.id
WHERE
gt.id = 27