Solved

How to write joint query?

Posted on 1998-10-30
1
945 Views
Last Modified: 2006-11-17
let's say i have a one table called order which contains productid, now how do i write the query which counts the number of orders which contains only two product (Ex Product A and Product B).
i try by writing one query which display product A and then second query which display product b and then i join two gather it works fine but how do i write everything in one query instead of three query.
0
Comment
Question by:bcp5190
1 Comment
 

Accepted Solution

by:
rscott_young earned 20 total points
ID: 1967279
Because you can't use "SUM" in your WHERE clause you'll have to use two queries.  Below is an example of both.  Use the first query to create a temporary table and the second to select your orders:

SELECT max([Order ID]),
sum(iif([Product ID] = 'A',1,0)) as PRODUCTA,
sum(iif([Product ID] = 'B',1,0)) as PRODUCTB,
sum(iif([Product ID] <> 'A' and [Product ID] <> 'B',1,0)) AS NONAB
FROM [Orders Detail]
GROUP BY [Order ID];

SELECT Order ID
FROM Temptable
WHERE PRODUCTA > 0 and PRODUCTB > 0 and NONAB = 0
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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