Report with data from 2 unrelated siblings

Discussion:

Frank Cazabon

2018-11-09 15:27:12 UTC

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

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
---

_______________________________________________
Post Messages to: ***@leafe.com
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/e4283eeb-cf5d-5eeb-64b3-***@gmail.com
** 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

Ted Roche

2018-11-09 15:49:29 UTC

Permalink

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%20of%20SQL-SELECT.pdf

and you might poke around on her website for others.

Post by Frank Cazabon
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,
INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (2, 2,
INSERT INTO Emails (EmailId, MerchantID, Email) VALUES (3, 4,
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
*Merchant**
* *Email**
* *Template*
Freddie
Brian
Template 1
Brian
Template 2
Roger
John
Template 1
John
Template 2
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]

_______________________________________________
Post Messages to: ***@leafe.com
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/CACW6n4sipnyte4g+CkFMcMEtVLQgshG99M3xdv+***@mail.gmail.com
** 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.

m***@mbsoftwaresolutions.com

2018-11-09 16:21:11 UTC

Permalink

Will putting a DISTINCT in your SELECT fix that?

_______________________________________________
Post Messages to: ***@leafe.com
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/***@mbsoftwaresolutions.com
** 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.

Frank Cazabon

2018-11-09 19:32:30 UTC

Permalink

No, that won't work.

Frank.

Frank Cazabon

Post by m***@mbsoftwaresolutions.com
Will putting a DISTINCT in your SELECT fix that?

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
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/fb9c2d47-be3f-050a-e869-***@gmail.com
** 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.

Frank Cazabon

2018-11-09 19:39:24 UTC

Permalink

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

Post by Ted Roche
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
http://www.tomorrowssolutionsllc.com/ConferenceSessions/Making%20the%20Most%20of%20SQL-SELECT.pdf
and you might poke around on her website for others.

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
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/5a4f8b95-f78c-b498-102f-***@gmail.com
** 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.

Charlie-gm

2018-11-10 20:46:49 UTC

Permalink

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

-Charlie

Post by Frank Cazabon
Hi,
CREATE CURSOR Merchants ;
(MerchantID i,;
MerchantName c(10))

[snip]

Post by Frank Cazabon
I need to create a report like this (the email and template columns
*Merchant**
* *Email**
* *Template*
Freddie

_______________________________________________
Post Messages to: ***@leafe.com
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/89463ff4-ff09-9a97-271d-***@gmail.com
** 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 wh

Frank Cazabon

2018-11-11 16:16:17 UTC

Permalink

Thanks Charlie.

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

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

Post by Frank Cazabon
Hi,
CREATE CURSOR Merchants ;
(MerchantID i,;
MerchantName c(10))

[snip]

Post by Frank Cazabon
I need to create a report like this (the email and template columns
*Merchant**
* *Email**
* *Template*
Freddie

[excessive quoting removed by server]

_______________________________________________
Post Messages to: ***@leafe.com
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/749349A8-3D37-4D21-BAB7-***@gmail.com
** 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 obviou