Query syntax

I need to set up a query and I'm not sure of the syntax. Here is the schema scenario:
A 'Company' table with companyID and companyName.
A 'Delivery Areas' table with areaID and areaDescription.
A CompanyDeliveryCrossReference table with a refID (just autonumber), companyID, and areaID

The cross reference table will have an entry for each area that the company delivers within. What would a query look like if it were looking for companys that delivered in all:Area1, Area2, Area3?
Thanks!
Amy
AmyLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bobaran98Commented:
Like this:

SELECT C.*
FROM Company C, CompanyDeliveryCrossReference R
WHERE R.areaID IN (1,2,3) AND R.companyID=C.companyID

Open in new window

0
Bobaran98Commented:
Actually, sorry, you'd want to use the following query to avoid duplicates (because with the previous query, if a company served both area 1 and area 2, it would show up twice in the list).

By the way, the autonumber refID isn't necessary for your CompanyDeliveryCrossReference table, since companyID and areaID can serve as a dual key (it's a many-to-many relationship).  However, if you choose to use an autonumber key field, it won't hurt anything.

SELECT DISTINCT C.*
FROM Company C, CompanyDeliveryCrossReference R
WHERE R.areaID IN (1,2,3) AND R.companyID=C.companyID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmyLAuthor Commented:
Thanks!!
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

AmyLAuthor Commented:
Hi!
I just noticed that the IN (1,2,3) is like '1 OR 2 OR 3'. Actually what I need is '1 AND 2 AND 3'. Is there a slight modification that I can make to accomplish this?

Thanks!
Amy
0
Bobaran98Commented:
Ah, I see.  Well, that's a very different matter.  There are a few different ways to approach the problem, and probably none of them will look very pretty. :-)  What makes this ugly to code is the fact that you're trying to match against three separate records, which means you have to find someway of accessing the cross reference table three times simultaneously.

First off, about what I said earlier regarding CompanyDeliveryCrossReference and the autonumber key-- There actually is a very good reason why you want to use a dual key (companyID and areaID) rather than an arbitrary autonumber key, and that's the fact that you should never have more than one row with those two values-- it would be an unnecessary duplicate.  By making those two fields a dual key, you're telling your database to prevent the addition of duplicate rows (which, of course, you should be careful in your code to avoid anyway).

I say all that now because, by preventing duplicates, you can also do something like this to solve your problem:

SELECT C.*
 
FROM Company C
 
WHERE
  (
    (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=1 AND companyID=C.companyID)
    + (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=2 AND companyID=C.companyID)
    + (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=3 AND companyID=C.companyID)
    = 3
  )

Open in new window

0
Bobaran98Commented:
Sorry, I let my parentheses get out of control.  Probably wouldn't make any difference, except for readability, but below is what I meant to type! :-)

Hope this works for you!

SELECT C.*
 
FROM Company C
 
WHERE
  (
    (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=1 AND companyID=C.companyID)
    + (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=2 AND companyID=C.companyID)
    + (SELECT COUNT(*) FROM CompanyDeliveryCrossReference WHERE areaID=3 AND companyID=C.companyID)
  ) = 3

Open in new window

0
AmyLAuthor Commented:
Wow, clever!! Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.