«

I have two tables, Advertisers and Ads. There is a one to many
relationship from Advertisers to Ads.

I want a listing of Advertisers that haven't placed any Ads.

I'm having a mental block on writing the SQL to pull that data.

Any ideas.

Thanks.

Brett

Brett_A wrote:
> I have two tables, Advertisers and Ads. There is a one to many
> relationship from Advertisers to Ads.
>
> I want a listing of Advertisers that haven't placed any Ads.
>
> I'm having a mental block on writing the SQL to pull that data.
>
Think "left outer join ... where ad.keycolumn is null"

Alternatively think about "WHERE NOT EXISTS"

With no details about your table key columns and your database, I really
can't get too specific without inventing something like this:

select v.* from
Advertisers v left join Ads a on v.AdvertiserID = a.AdvertiserID
WHERE a.AdID is null

or

SELECT * FROM Advertisers v
WHERE NOT EXISTS (
select * from Ads a WHERE v.AdvertiserID = a.AdvertiserID)


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

On Jan 9, 1:10 pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
> Brett_A wrote:
> > I have two tables, Advertisers and Ads. There is a one to many
> > relationship from Advertisers to Ads.
>
> > I want a listing of Advertisers that haven't placed any Ads.
>
> > I'm having a mental block on writing the SQL to pull that data.
>
> Think "left outer join ... where ad.keycolumn is null"
>
> Alternatively think about "WHERE NOT EXISTS"
>
> With no details about your table key columns and your database, I really
> can't get too specific without inventing something like this:
>
> select v.* from
> Advertisers v left join Ads a on v.AdvertiserID = a.AdvertiserID
> WHERE a.AdID is null
>
> or
>
> SELECT * FROM Advertisers v
> WHERE NOT EXISTS (
> select * from Ads a WHERE v.AdvertiserID = a.AdvertiserID)
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Got it Bob, thanks!

Brett