Help With Joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Help With Joins

Post 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...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What's the purpose of the table receipients? Isn't it a doublet/subset of the data in users?
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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!
Post Reply