Query/Join Property Issues

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help determining a solution to a fairly simple situation (I think).
In a db, I have two tables with data. One table represents things that have
been requested, and the other table represents things that have been
confirmed. I need to "link" the two tables together, and handle any
exceptions - which would be things requested that were not confirmed.
Ordinarily, this would be a simple query to write, using the JOIN feature.

The twist comes in this specific situation --> If I have an instance where
two of the same thing have been requested, I need to be able to show that one
was confirmed, but the second is an exception. I have tried all varieties of
options, from unique records, unique values, etc, and nothing seems to truly
produce the results I am looking for. Any help here would be greatly
appreciated.

As an example, table one might contain these values: dress, car, doll,
wagon, car, box, car. Table two might contain these values: doll, car,
truck, lollipop, dress, car.

Thanks!
 
DebCMom said:
I need help determining a solution to a fairly simple situation (I
think). In a db, I have two tables with data. One table represents
things that have been requested, and the other table represents
things that have been confirmed. I need to "link" the two tables
together, and handle any exceptions - which would be things requested
that were not confirmed. Ordinarily, this would be a simple query to
write, using the JOIN feature.

The twist comes in this specific situation --> If I have an instance
where two of the same thing have been requested, I need to be able to
show that one was confirmed, but the second is an exception. I have
tried all varieties of options, from unique records, unique values,
etc, and nothing seems to truly produce the results I am looking for.
Any help here would be greatly appreciated.

As an example, table one might contain these values: dress, car, doll,
wagon, car, box, car. Table two might contain these values: doll,
car, truck, lollipop, dress, car.
Click to expand...

I take it that there's no way to logically connect a specific request
record with a specific confirmation record. So all you know is that 3
cars were requested, and 2 cars were confirmed.

So maybe all you need the query to return is all the items where the
number requested is less than the number confirmed. In that case, you
can compare two queries that group on the items in each table and count
the values; something like (air SQL):

SELECT
R.Item,
ReqCount - Nz(CnfCount, 0) As Needed
FROM
(
SELECT
Item,
Count(*) As ReqCount
FROM
Requests
GROUP BY
Item
) R
LEFT JOIN
(
SELECT
Item,
Count(*) As CnfCount
FROM
Confirmations
GROUP BY
Item
) C
ON R.Item = C.Item
WHERE
(ReqCount - Nz(CnfCount, 0)) > 0
 
Back
Top