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, "brian@queen.com") INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (2, 2, "brian@gmail.com") INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (3, 4, "john@queen.com")
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 brian@queen.com Template 1 Brian brian@gmail.com Template 2 Roger John john@queen.com 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?