Write SQL Query Code Problem

Discussion in 'ASP General' started by Brett_A, Jan 9, 2008.

  1. Brett_A

    Brett_A Guest

    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, Jan 9, 2008
    #1
    1. Advertising

  2. 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.
    Bob Barrows [MVP], Jan 9, 2008
    #2
    1. Advertising

  3. Brett_A

    Brett_A Guest

    On Jan 9, 1:10 pm, "Bob Barrows [MVP]" <>
    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
    Brett_A, Jan 9, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Edward
    Replies:
    4
    Views:
    4,597
    William \(Bill\) Vaughn
    Apr 10, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,455
    Anonymous
    Oct 13, 2005
  3. drlexkingdome
    Replies:
    1
    Views:
    821
    Mark McIntyre
    Dec 19, 2004
  4. Belinda
    Replies:
    4
    Views:
    354
    Bob Barrows [MVP]
    Jun 11, 2004
  5. babu nair
    Replies:
    1
    Views:
    676
    Sandor Szücs
    Jul 17, 2008
Loading...

Share This Page