Page 1 of 1

Help With Joins

Posted: Fri Oct 06, 2006 5:42 pm
by nickvd
I have the following table layout for greetings/users(authors)/recipients

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
);
Hrm... stupid me submitted b4 asking my question...

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
Which works fine, but I'd like to also get the recipients the message was destined for, there could be many of them... the greetings2recipients has a many to many relationship...

Posted: Fri Oct 06, 2006 6:38 pm
by volka
What's the purpose of the table receipients? Isn't it a doublet/subset of the data in users?

Posted: Fri Oct 06, 2006 7:08 pm
by nickvd
volka wrote:What's the purpose of the table receipients? Isn't it a doublet/subset of the data in users?
No, the recipients are just names/emails for the people who will receive the message... You can think of the system as a private messaging system, where the recipients do not need accounts on the system... It's tough to explain (it wasnt explained all that well to me) If i have time, i'll post the details i was given...

Posted: Fri Oct 06, 2006 7:25 pm
by volka
try

Code: Select all

SELECT
	greetings.id,
	greetings.title,
	greetings.time_stamp,
	greetings.message,
	sender.username,
	recipients.name,
	recipients.email
FROM
	greetings
LEFT JOIN
	users as sender
ON
	greetings.users_id = sender.id
LEFT JOIN
	greetings_to_recipients as g2r
ON
	greetings.id=g2r.greeting_id
LEFT JOIN
	recipients
ON
	g2r.recipient_id=recipients.id
WHERE
	greetings.id = 27
(completely untested)

Posted: Fri Oct 06, 2006 10:07 pm
by nickvd
Woot.... It works like a charm. It returns one result for each recipient... a simple task to turn into a single result with an array of recipients.

Thanks man!