0

I have two tables:

create table xyz
(campaign_id varchar(10)
,account_number varchar)

Insert into xyz
values ( 'A', '1'), ('A', '5'), ('A', '7'), ('A', '9'), ('A', '10'),
       ( 'B', '2'), ('B', '3'),
       ( 'C', '1'), ('C', '2'), ('C', '3'), ('C', '5'), ('C', '13'), ('C', '15'),
       ('D', '2'), ('D', '9'), ('D', '10')


create table abc
(account_number varchar)

insert into abc
values ('1'), ('2'), ('3'), ('5')

Now, I want to write a query where all the four account_number 1, 2, 3, 5 are included in a Campaign_id.

The answer is C.

[My aim is to find the Campaign Code that includes account_number 1, 2, 3 & 5. This condition is only satisfied by campaign code C.]

I tried using IN and ALL, but don't work. Could you please help.

SouravD
  • 127
  • 1
  • 12

3 Answers3

1

I think what you are after is a inner join. Not sure from your questions which way around you want your data. However this should give you a good clue how to procede and what keywords to lock for in the documentation to go further.

SELECT a.*
FROM xyz a
INNER JOIN abc b ON b.account_number = a.account_number;

EDIT:

Seems I misunderstood the original question.. sorry. To get what you want you can just do:

SELECT  campaign_id
FROM    xyz 
WHERE   account_number IN ('1', '2', '3', '5')
GROUP BY campaign_id
HAVING  COUNT(DISTINCT account_number) = 4;

This is called relational division if you want to investigate further.

M.Scherzer
  • 921
  • 7
  • 9
  • This will list the campaigns that have any of the account numbers in `abc`, not just the ones that have _all_ the account numbers. – Barmar May 01 '14 at 01:18
  • misunderstood the question I guess, sounded to me like he just wanted to find the joins. From your answer my assumption was wrong. – M.Scherzer May 01 '14 at 01:21
  • Sorry, if I sounded confusing. My aim is to find the Campaign Code that includes account_number 1, 2, 3 & 5. This condition is only satisfied by campaign code C. I don't know how to write a query for it. – SouravD May 01 '14 at 01:23
  • @M.Scherzer That's why we like posters to provide the desired output, it helps clarify things like this. He said that the expected result was just `C`, but your query returns `A`, `B`, `C`, and `D`. – Barmar May 01 '14 at 01:35
  • yeah got that I ammended my anwser with the correct SQL which I think is nice and concise, no need for any joins. – M.Scherzer May 01 '14 at 01:47
1
SELECT campaign_id
FROM (
    SELECT campaign_id, COUNT(*) AS c, total_accounts
    FROM xyz
    JOIN abc ON xyz.account_number = abc.account_number
    CROSS JOIN (SELECT COUNT(*) AS total_accounts
                FROM abc) AS x
    GROUP BY campaign_id
    HAVING c = total_accounts) AS subq

DEMO

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you for this, Barmer. It is giving me a strange result though. It gives two blank columns. One Campaign_id, the other is C. – SouravD May 01 '14 at 01:28
  • I've modified the query to just return the `campaign_id` column by moving all the work into a subquery. – Barmar May 01 '14 at 01:32
  • It clearly works in MySQL, as the demo shows. If you're using a different DBMS, you may have to tweak the syntax. – Barmar May 01 '14 at 01:38
  • Please tag your questions with the DBMS you use, to ensure that you get appropriate answers. – Barmar May 01 '14 at 01:38
  • I'm not familiar with Sybase, so I don't know how its syntax differs. It's also not available on sqlfiddle.com, so I can't even test my answer with it. You'll have to get further help from someone else. – Barmar May 01 '14 at 01:42
0
select xyz.campaign_id
from xyz
join abc
on xyz.account_number = abc.account_number
group by xyz.campaign_id
having count(xyz.campaign_id) = 
(select count(account_number) from abc);

Caution: t-sql implementation

user353gre3
  • 2,747
  • 4
  • 24
  • 27