...

This was my attempt to build the data, but I get doubling up:

Will putting a DISTINCT in your SELECT fix that?

Thanks Ted,

Tamar's article doesn't seem to help, her example has one record per unrelated sibling whereas I can have none or many. Maybe I'll tackle this on Monday when the brain is fresh.

Frank.

Frank Cazabon

On 09/11/2018 11:49 AM, Ted Roche wrote:

I know you can do this in SQL, but it's Friday and I'm too tired to work it out again :)

Tamar is a great authority on SQL (she wrote a book on it!) and I know she wrote several articles on unrelated siblings. Here's one:

http://www.tomorrowssolutionsllc.com/ConferenceSessions/Making%20the%20Most%...

and you might poke around on her website for others.

On Fri, Nov 9, 2018 at 10:27 AM Frank Cazabon frank.cazabon@gmail.com wrote:

...

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?

--

Frank.

Frank Cazabon

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html

---

[excessive quoting removed by server]

Thanks Charlie.

Unless the SQL is really complex I find SQL much easier to maintain than procedural code.

On 10 November 2018 16:46:49 GMT-04:00, Charlie-gm ccbibleman@gmail.com wrote:

I'd recommend building it up procedurally. Performance might be a little slower but the end result will be far easier to maintain.

-Charlie

On 11/9/2018 10:27 AM, Frank Cazabon wrote:

...

Hi,

CREATE CURSOR Merchants ; (MerchantID i,; MerchantName c(10))

[snip]

...

I need to create a report like this (the email and template columns must be side by side): *Merchant** *     *Email** *     *Template* Freddie

[excessive quoting removed by server]