Using AND or OR on subtable

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I have a typical product ordering db with 3 tables (orders, products and
orders_products... all self-explanatory!!)

Now I need to create a query that brings back all orders that have, for
example, product 1 AND product 2.

The problem is that after doing the joins and I add the where clause like:
WHERE (products.id = 1 AND products.id = 2) then it finds nothing. And if I
do: WHERE (product.id = 1 OR products.id = 2) then it brings all orders that
have products 1 or 2, even though some orders don't have both products.

Any ideas to implement this?

Thanks,
Ivan
 
Hi, Ivan.
The problem is that after doing the joins and I add the where clause like:
WHERE (products.id = 1 AND products.id = 2) then it finds nothing.
Click to expand...

This is because the ID can't be 1 and 2 at the same time in the same record.
Any ideas to implement this?
Click to expand...

Yes. Please see the following Web page for a link to the tip, "How to
determine which customers purchased both products" in the Queries section:

http://www.Access.QBuilt.com/html/how-to_tips.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Dear Ivan:

If you table structure is anything like I expect it to be, you could query
the orders table using two very similar criterion. Not knowing your table
or column names, here's a wild guess:

SELECT OrderNumber
FROM Order O
WHERE EXISTS(SELECT *
FROM OrderDetail OD
WHERE OD.OrderNumber = O.OrderNumber
AND OD.ProductID = 1)
AND EXISTS(SELECT *
FROM OrderDetail OD
WHERE OD.OrderNumber = O.OrderNumber
AND OD.ProductID = 2)

This will return all the orders having both products. You could change the
first line to:

SELECT DISTINCT CustomerNumber

This may be more like what you want, but I wanted to show the slightly more
direct concept first.

Would there be any interest in finding customers that have ordered 1 and 2
whether on the same order or not?

Tom Ellison
 
Hi,

As in the case were you want DesiredSkills (one field, SkillID, primary
key), and get all employee that have all these skills? If the
CandidatesSkills (candidateID, skillID) list the skills for each candidate
(one skill per row, no duplication on the pair: candidateID-skillID), then:


SELECT candidateID
FROM CandidatesSkills I AS c INNER JOIN DesiredSkills AS d
ON c.skillID=c.skillID
GROUP BY candidateID
HAVING COUNT(*)=(SELECT COUNT(*) FROM DesiredSkills)


will do.


Indeed, the inner join moves "out of the picture" irrelevant skill (those we
don't "desire") and if a candidate has still a count of skills (left, after
the filtering from the inner join) equal to the number of desired skills,
that candidate has all the desired skills.

Take a look at http://www.mvps.org/access/queries/qry0016.htm for variations
on that idea.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top