Solved

How to write joint query?

Posted on 1998-10-30
1
941 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now