Solved

Query syntax

Posted on 2009-07-12
7
278 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:AmyL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24836315
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
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 250 total points
ID: 24836321
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
 

Author Closing Comment

by:AmyL
ID: 31602652
Thanks!!
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Author Comment

by:AmyL
ID: 24836528
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
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24836756
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
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24836759
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
 

Author Comment

by:AmyL
ID: 24836955
Wow, clever!! Thanks!
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question