Hi,
CREATE CURSOR Merchants ;
(MerchantID i,;
MerchantName c(10))
INSERT INTO Merchants (MerchantID, MerchantName) VALUES (1, "Freddie")
INSERT INTO Merchants (MerchantID, MerchantName) VALUES (2, "Brian")
INSERT INTO Merchants (MerchantID, MerchantName) VALUES (3, "Roger")
INSERT INTO Merchants (MerchantID, MerchantName) VALUES (4, "John")
CREATE CURSOR Emails;
(EmailID i,;
MerchantID i,;
Email c(30))
INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (1, 2,
"[email protected]")
INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (2, 2,
"[email protected]")
INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (3, 4,
"[email protected]")
CREATE CURSOR Templates ;
(TemplateID i,;
TemplateName c(10))
INSERT INTO Templates (TemplateID, TemplateName) VALUES (1, "template 1")
INSERT INTO Templates (TemplateID, TemplateName) VALUES (2, "template 2")
CREATE CURSOR MerchantTemplates;
(MerchantTemplateID i,;
MerchantID i,;
TemplateID i)
INSERT INTO MerchantTemplates (MerchantTemplateID, MerchantID,
templateID) VALUES (1, 2, 1)
INSERT INTO MerchantTemplates (MerchantTemplateID, MerchantID,
templateID) VALUES (2, 2, 2)
INSERT INTO MerchantTemplates (MerchantTemplateID, MerchantID,
templateID) VALUES (3, 4, 1)
INSERT INTO MerchantTemplates (MerchantTemplateID, MerchantID,
templateID) VALUES (4, 4, 2)
I need to create a report like this (the email and template columns must
be side by side):
*Merchant**
* *Email**
* *Template*
Freddie
Brian
[email protected]
Template 1
Brian
[email protected]
Template 2
Roger
John
[email protected]
Template 1
John
Template 2
This was my attempt to build the data, but I get doubling up:
SELECT MerchantName;
,Email;
,TemplateName ;
FROM Merchants ;
LEFT JOIN Emails ON Merchants.MerchantID = Emails.MerchantID;
LEFT JOIN MerchantTemplates ON Merchants.MerchantID =
MerchantTemplates.MerchantID;
LEFT JOIN Templates ON MerchantTemplates.TemplateId =
Templates.TemplateID
Can I do this in SQL or do I need to build my cursor up procedurally?
--
Frank.
Frank Cazabon
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
