SQL & PL/SQL

The business scenario is this. We have leads (potential customers) that are assigned to markets (city names). There are zip codes that overlap these different markets, so you can have 2 customers with the same zip code assigned to two different markets. Example John Smith, zip 85377 assigned to Carefree market and Sally Jones zip 85377 assigned to Scottsdale Market. When planning mail to leads we want to be able to know how many leads are in an overlapping market. There are sometimes two or more overlapping markets. I have three tables shown below (testleads, testoverlap, and testmarket). The market table has the market and related overlap markets (2 in this example). The testoverlap table is a list of all overlapping zips and related markets. The testleads has a leadid, zip, and market. What I want to do is run a query that tells me for a given market (Scottsdale) how many leads exist in the overlapping markets (in this case, Chandler and Carefree) where the testleads.zip exists in testoverlap.zip. In other words, I don't want all leads in the overlap market, but only those that are in the overlapping zips - as only a portion of the markets overlap.


The below query works to tell me I have one lead in the overlap market of Chandler but any effort I make to see the # of leads in both overlap markets won't work. Can you help?

{code

create table testmarket (
market varchar2(20), OL_Market1 varchar2(20), OL_Market2 varchar2(20));

create table testoverlap (
zip varchar2(5),market varchar2(20));

create table testleads (
leadid number(3), zip varchar(5), market_id varchar2(20));

insert into testmarket (market, OL_Market1, OL_Market2) values ('Scottsdale', 'Chandler', 'Carefree');

insert into testoverlap (zip, market) values ('85377', 'Carefree');
insert into testoverlap (zip, market) values ('85022', 'Chandler');
insert into testoverlap (zip, market) values ('60311', 'Gurnee');

insert into testleads (leadid, zip, market_id) values (123, '85377', 'Caqrefree');
insert into testleads (leadid, zip, market_id) values (145, '85024', 'Scottsdale');
insert into testleads (leadid, zip, market_id) values (167, '85377','Scottsdale');
insert into testleads (leadid, zip, market_id) values (189, '85022', 'Chandler');

Select * from testmarket;
Market OL_Market1 OL_Market2
Scottsdale Chandler Carefree

Select * from testoverlap;

Zip Market
85377 Carefree
85022 Chandler
60311 Gurnee

Select * from testleads;

Leadid zip market_id
123 85377 Carefree
145 85024 Scottsdale
167 85377 Scottsdale
189 85022 Chandler


Select count(leadid) from testleads
where testleads.zip = (select testoverlap.zip from testoverlap where testoverlap.market = (select OL_market1 from testmarket where market = 'Scottsdale'))
AND testleads.market_id = (select OL_market1 from testmarket where market = 'Scottsdale');


}