Over my head SQL - need help from a guru

Results 1 to 3 of 3

Thread: Over my head SQL - need help from a guru

  1. #1
    Ben Roy Guest

    Default Over my head SQL - need help from a guru

    Quick summary:<BR><BR>Dealers and Counties. Each dealer can service multiple counties and each county could have multiple dealers. So we&#039;ve got a dealers table, a counties table, and a DealerCounties table, which is just a linking table that stores a DealerID and a County ID. What I need to do is pull a list of all the counties for which a dealer is NOT authorized. So in pseudo-SQL we have<BR><BR>SELECT CountyID, CountyName, DealerID FROM DealerCounties, Counties WHERE &#060;no record exists for this county and dealer&#062;<BR><BR>Hopefully someone is understanding what I want here, because I&#039;m having a hard time describing it. The SQL I tried to use first is:<BR><BR>SELECT Counties.ID, Counties.County<BR>FROM Counties LEFT JOIN DealerCounties ON Counties.ID = DealerCounties.CountyID<BR>WHERE (((DealerCounties.DealerID)&#060;&#062;2));<BR><BR >This works just dandy when you only have one dealer. The problem is that if DealerID 1 has a record for a county, it pulls when you look for &#060;&#062;2, even if there is also a record DealerID 2. Bottom line, there are counties that are showing up as not covered by a dealer that actually are covered by a dealer. I think I need some kind of setup with a subquery but I don&#039;t know anything about that.<BR><BR>HELP!

  2. #2
    Join Date
    Dec 1969

    Default Hmmm

    select counties.county_id from counties, dealercounties where counties.county_id not in (select county_id from dealercounties where dealercounties.dealerid = 2)<BR><BR>-- whol

  3. #3
    Ben Roy Guest

    Default Yipee!

    And that&#039;s why I come to this board. Thanks a ton.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts