Identify connection between two columns and insert corresponding values

I have the following table which you can also find in the SQL fiddle here:

CREATE TABLE Products (
    Eventtype TEXT,
    CampaignID TEXT,
    Product TEXT,
    Quantity VARCHAR(255)
);

INSERT INTO Products
(Eventtype, CampaignID, Product, Quantity)
VALUES 

("Event_01", "9857", NULL, "500"),
("Event_01", "5504", NULL, "670"),
("Event_01", "4589", NULL, "950"),
("Event_01", "77801", NULL, "750"),

("Event_02", "9857", "Product A", "300"),
("Event_03", "9857", "Product A", "250"),
("Event_02", "77801", "Product A", "350"),
("Event_03", "77801", "Product A", "150"),

("Event_02", "5504", "Product B", "270"),
("Event_03", "5504", "Product B", "850"),
("Event_02", "4589", "Product B", "420"),
("Event_03", "4589", "Product B", "630");

I use the following query:

Select Eventtype, CampaignID, Product, Quantity
FROM Products
WHERE Product = "Product B";

All this works fine so far.

Event_01 only has a CampaignID.
Event_02 and Event_03 have a CampaignID and Products.

Now I want that if I select Product B in the query the corresponding values on CampaignID level for Event_01 are inserted into the results.

Basically, Event_02 and Event_03 should be used to identify that Product B is connected to a certain CampaignID and then the quantity for this CampaignID in Event_01 is displayed:

The desired result should look like this:

Eventtype         CampaignID         Product         Quantity
Event_01          4589               NULL               950
Event_01          5504               NULL               670
Event_02          4589               Product B          420
Event_02          5504               Product B          270
Event_03          4589               Product B          630
Event_04          5504               Product B          850

How do I have to modify my query to achieve this result?

Answer

With reference to this this question the solution is:

SELECT Eventtype, CampaignID, Product, Quantity
FROM Products
WHERE 
CampaignID IN
   (Select CampaignID
    FROM Products
    WHERE Product = "Product B")
 GROUP BY 1,2;

http://www.sqlfiddle.com/#!9/c6b4322/1

Advertisement

Answer