[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Need Query for this problem

I have 3 tables.... Customers, Campaigns, and Campaign Customers (Campaign Customers just contains the ID of Customers and Campaigns). I Need the query to produce the following info:

Select each customer who is in a campaign
          Order the data by last name, first name, and campaign name
          Return the following information:
              - last name
              - first name
              - campaign name
              - the sequence number of the customer in the list
              - the sequence number of the campaign for each customer
              - the number of campaigns to which each customer belongs

         Example:

 LastName   FirstName   CampaignName    Customer_Seq    Campaign_Seq    Campaign_Count
    --------          ---------   ------------            ------------                  ------------        --------------
    Doe             Jane        First Campaign              1                             1                          2
    Doe            Jane        Next Campaign               1                             2                          2
    Hungry        I             First Campaign                2                             1                          1
    Public          John        First Campaign              3                             1                           3
    Public          John        Last Campaign              3                              2                          3
    Public          John        Next Campaign              3                             3                           3
    Smith          Short       First Campaign               4                             1                           1
0
tim0822
Asked:
tim0822
  • 6
  • 4
  • 2
  • +4
1 Solution
 
Patrick MatthewsCommented:
Hello tim0822,

What database are you using?

Regards,

Patrick
0
 
Helen FeddemaCommented:
Can you post the database?
0
 
zadeveloperCommented:
with very little to work with - I guessed your Tables and RI

select 
	 c.LastName,
	 c.FirstName,
	 cp.CampaignName,
	 cpc.Customer_Seq,
	 cpc.Campaign_Seq,
	 scount(cpc.*) as [Campaign_Count]
from
	Customers c
	inner join Campaigns cp on cp.CustomerID = c.CustomerID
	inner join CampaignCustomers  cpc on cpc.CustomerID = c.CustomerID and cpc.CampaignID = c.CampaignID
group by
	c.LastName,
	 c.FirstName,
	 cp.CampaignName,
	 Customer_Seq,
	 Campaign_Seq
	

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tim0822Author Commented:
The database is SQL and the _seq and the _count are not fields in any of the tables these need to be calculated
0
 
tim0822Author Commented:
here is the code to create database and data:

-- table of customers
CREATE TABLE dbo.Customers
(
    CustomerID          int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    FirstName           varchar(30) NOT NULL,
    MiddleInitial       char(1),
    LastName            varchar(30) NOT NULL
)

-- table of campaigns
CREATE TABLE dbo.Campaigns
(
    CampaignID          int NOT NULL PRIMARY KEY IDENTITY(1, 1),
    CampaignName        varchar(50) NOT NULL,
    CampaignDesc        varchar(255) NOT NULL
)

-- table of customers who belong to a campaign
CREATE TABLE dbo.CampaignCustomers
(
    CampaignID          int NOT NULL,
    CustomerID          int NOT NULL,
    IsActive            bit NOT NULL DEFAULT(1)

INSERT INTO dbo.Customers(FirstName, MiddleInitial, LastName)
SELECT 'Jane', NULL, 'Doe'
UNION ALL SELECT 'John', NULL, 'Doe'
UNION ALL SELECT 'John', 'Q', 'Public'
UNION ALL SELECT 'Short', 'E', 'Smith'
UNION ALL SELECT 'I', 'M', 'Hungry'

INSERT INTO dbo.Campaigns(CampaignName, CampaignDesc)
SELECT 'First Campaign', 'My first campaign'
UNION ALL SELECT 'Next Campaign', 'My next campaign'
UNION ALL SELECT 'Last Campaign', 'My last campaign'

INSERT INTO dbo.CampaignCustomers(CampaignID, CustomerID)
SELECT 2, 3
UNION ALL SELECT 2, 1
UNION ALL SELECT 1, 3
UNION ALL SELECT 3, 3
UNION ALL SELECT 1, 4
UNION ALL SELECT 1, 5
UNION ALL SELECT 1, 1

INSERT INTO dbo.Exclude(CampaignID, CustomerID)
SELECT 2, 2
UNION ALL SELECT 2, 1
UNION ALL SELECT 1, 4
UNION ALL SELECT 3, 3
UNION ALL SELECT 1, 2
0
 
Patrick MatthewsCommented:
SELECT cu.LastName, cu.FirstName, ca.CampaignName,
      (SELECT COUNT(cu2.CustomerID)
      FROM Customers cu2
      WHERE cu2.LastName <= cu.LastName AND cu2.FirstName <= cu.FirstName) AS Customer_Seq,
      (SELECT COUNT(cu3.CustomerID)
      FROM Customers cu3 INNER JOIN
            CampaignCustomers cc3 ON cu3.CustomerID = cc3.CustomerID INNER JOIN
            Campaigns ca3 ON cc3.CampaignID = ca3.CampaignID
      WHERE cc3.IsActive = 1 AND cu3.LastName <= cu.LastName AND cu3.FirstName <= cu.FirstName AND
            ca3.CampaignName <= ca.CampaignName) AS Campaign_Seq,
      (SELECT COUNT(cu4.CustomerID)
      FROM Customers cu4 INNER JOIN
            CampaignCustomers cc4 ON cu4.CustomerID = cc4.CustomerID
      WHERE cc4.IsActive = 1) AS Campaign_Count
FROM Customers cu INNER JOIN
      CampaignCustomers cc ON cu.CustomerID = cc.CustomerID INNER JOIN
      Campaigns ca ON cc.CampaignID = ca.CampaignID
ORDER BY cu.LastName, cu.FirstName, ca.CampaignName
0
 
tim0822Author Commented:
matthewspatrick:
This is close but I think there needs to be some grouping. The grouping part is what I could not get.
The data needs to look like my example:

LastName   FirstName   CampaignName    Customer_Seq    Campaign_Seq    Campaign_Count
    --------          ---------   ------------            ------------                  ------------        --------------
    Doe             Jane        First Campaign              1                             1                          2
    Doe            Jane        Next Campaign               1                             2                          2
    Hungry        I             First Campaign                2                             1                          1
    Public          John        First Campaign              3                             1                           3
    Public          John        Last Campaign              3                              2                          3
    Public          John        Next Campaign              3                             3                           3
    Smith          Short       First Campaign               4                             1                           1

0
 
shru_0409Commented:
SELECT cu.CustomerID , cu.LastName, cu.FirstName, ca.CampaignName,         
         DENSE_RANK() OVER (ORDER BY cu.LastName ASC) AS Customer_Seq,
row_number() over ( partition by cu.CustomerID order by  ca.CampaignName ) AS Campaign_Seq,
COUNT(cu.CustomerID) OVER ( partition by cu.LastName)  Campaign_Count
FROM Customers cu INNER JOIN  CampaignCustomers cc ON cu.CustomerID = cc.CustomerID INNER JOIN
      Campaigns ca ON cc.CampaignID = ca.CampaignID
ORDER BY cu.LastName, cu.FirstName, ca.CampaignName


this is oracle ssntax..
0
 
SharathData EngineerCommented:
try this query.

Can you provide clarification to these questions.

1) There is no relation between Customer 2 and Campaign 1 in CampaignCustomers table. Then how do you want that record in your expected result?
2) Why don't you want Customer 5 and Campaign 1 combination in your expected result when there is relationship in CampaignCustomers table?
3) What is the purpose of Exclude table?
select C1.LastName,C1.FirstName,C.CampaignName,CC.CustomerID as Customer_Seq,C.CampaignID as Campaign_Seq,t1.CampaignID_Count
  from Customers C1
  join CampaignCustomers CC 
    on C1.CustomerID = CC.CustomerID  
  join Campaigns C
    on C.CampaignID = CC.CampaignID
  join (select CustomerID,count(CampaignID) as CampaignID_Count from CampaignCustomers group by CustomerID) as t1
    on CC.CustomerID = t1.CustomerID
 order by CC.CustomerID,C.CampaignID

/* output

Doe	Jane	First Campaign	1	1	2
Doe	Jane	Next Campaign	1	2	2
Public	John	First Campaign	3	1	3
Public	John	Next Campaign	3	2	3
Public	John	Last Campaign	3	3	3
Smith	Short	First Campaign	4	1	1
Hungry	I	First Campaign	5	1	1
*/

Open in new window

0
 
js-profiCommented:
do the homework rules not apply in that thread?
0
 
Patrick MatthewsCommented:
tim0822,

I ended up reworking this to use temp tables.




SELECT IDENTITY(int, 1, 1) AS ID, cu.CustomerID, cu.LastName, cu.FirstName, COUNT(*) AS TotCamp
INTO #Cust
FROM Customers cu INNER JOIN
      CampaignCustomers cc ON cu.CustomerID = cc.CustomerID
WHERE cc.IsActive = 1
GROUP BY cu.CustomerID, cu.LastName, cu.FirstName
ORDER BY cu.LastName, cu.FirstName, cu.CustomerID

SELECT IDENTITY(int, 1, 1) AS ID, cu.ID AS CuID, cu.CustomerID, cu.LastName, cu.FirstName, ca.CampaignID,
      ca.CampaignName
INTO #Camp
FROM #Cust cu INNER JOIN
      CampaignCustomers cc ON cu.CustomerID = cc.CustomerID INNER JOIN
      Campaigns ca ON cc.CampaignID = ca.CampaignID
WHERE cc.IsActive = 1
GROUP BY cu.ID, cu.CustomerID, cu.LastName, cu.FirstName, ca.CampaignName, ca.CampaignID
ORDER BY cu.ID, cu.CustomerID, cu.LastName, cu.FirstName, ca.CampaignName, ca.CampaignID

SELECT cu.LastName, cu.FirstName, ca.CampaignName,
      (SELECT COUNT(cu2.ID)
      FROM #Cust cu2
      WHERE cu2.ID <= cu.ID) AS Customer_Seq,
      (SELECT COUNT(ca2.ID)
      FROM #Camp ca2
      WHERE ca2.CuID = ca.CuID AND ca2.ID <= ca.ID) AS Campaign_Seq,
      cu.TotCamp AS Campaign_Count
FROM #Cust cu INNER JOIN
      #Camp ca ON cu.CustomerID = ca.CustomerID
ORDER BY ca.ID

DROP TABLE #Cust
DROP TABLE #Camp




Patrick
0
 
Patrick MatthewsCommented:
js-profi,

My "homework detector" was not buzzing on this question, but if you think this was probably for an academic
assignment then you should use the Request Attention link to bring it to the Moderators' attention.

Patrick
0
 
tim0822Author Commented:
I was trying to figure out a way to do this without the use of temp tables but I guess there is no way around it.
0
 
tim0822Author Commented:
js-profi,

This was not a "homework assignment" it is an actual problem I needed to solve. While the data and tables were created so I could post the question on this site I assure you the problem is real.
0
 
tim0822Author Commented:
matthewspatrick,

Thank you for all of your help!
0
 
SharathData EngineerCommented:
tim0822 -  I have not seen any comments in my post. Did you try that query?
0
 
js-profiCommented:
i may be wrong but the initial q. is an assignment "select each data ... order the data ..."
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now