Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Write SQL Query Code Problem

Reply
Thread Tools

Write SQL Query Code Problem

 
 
Brett_A
Guest
Posts: n/a
 
      01-09-2008
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
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-09-2008
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.


 
Reply With Quote
 
 
 
 
Brett_A
Guest
Posts: n/a
 
      01-09-2008
On Jan 9, 1:10 pm, "Bob Barrows [MVP]" <(E-Mail Removed)>
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write SQL query inside javascript block babu nair Ruby 1 07-17-2008 10:08 AM
What Happened to Sql Enterprise Manager and Sql Query Analyser in Visual Studio 2005 Edward ASP .Net 4 04-10-2006 04:15 PM
Build dynamic sql query for JSTL <sql:query> Anonymous Java 0 10-13-2005 10:01 PM
How to write XML from a SQL Server query, to a ASP.NET textBox drlexkingdome C Programming 1 12-19-2004 11:25 PM



Advertisments