Count vs Distinct

[Ivan Debono]

Hi all,

I need to create a query based on a master-details tables scenario, where a
record of the master table can have multiple details records.

For example:
Master Table has 3 records with just 1 field called id (values 1, 2 and 3)

Details table is like this
master_id id
1 10
2 10
2 20
2 30
3 20
3 20

I want to find out because master records have more than 1 detail record and
those detail records are the same.

From the above details table, master id 1 is not included because it has
just 1 details record. master id 2 has 3 details records but their ids are
different. Only master id 3 should be returned.

Thanks,
Ivan

 

[[MVP] S.Clark]

Either
Select master_id, count(id) from details GROUP BY master_id having count(id)

1

or
Select master_id, count(id) from details GROUP BY master_id where count(id)

1

One of those should get you close.

 

[Guest]

Try this --
SELECT MasterID.master_ID, MasterID.ID
FROM MasterID
GROUP BY MasterID.master_ID, MasterID.ID
HAVING (((Count(MasterID.ID))>1));

 

[Ivan Debono]

That gives me all master records that have more than 1 details record. How
can I expand the statement to get all those master records that have all
detail id's the same?

Ivan

 

[Guest]

This does give you all the master records that have multiple detail id's the
same.

 

[John Spencer]

THe following might work for you.

SELECT M.Master_ID, Count(D.ID) as UniqueValues
FROM Master_Table as M INNER JOIN
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
ON M.MasterID = D.Master_ID
GROUP BY M.MasterID, D.ID
HAVING Count(D.ID) > 1

If that doesn't work. Build a query getting the distinct values of MasterID
and DetailID on the details table and then join it the master table and do a
count there.

 

[Michel Walsh]

Hi,


SELECT *
FROM masterID
WHERE master_ID IN( SELECT master_ID
FROM masterID
GROUP BY master_ID, id
HAVING COUNT(*) >1)



is a possible solution.


Hoping it may help,
Vanderghast, Access MVP

 

[Ivan Debono]

It's half the way though!!

It does return all master records that have more than 1 details record. But
I need all master records where count(details) > 1 AND distinct(details)=1
!!

This means that all the id's of the details for a particular master record
are all the same.

Ivan

 

[John Spencer]

This may work for you

SELECT Master.Master_ID
FROM Master INNER JOIN Details
ON Master.Master_ID = Details.Master_ID
WHERE Master.Master_ID IN
(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
HAVING Count(D.MasterID) = 1)
GROUP BY Master.Master_ID
HAVING Count(Master.Master_ID) > 1

 

[Ivan Debono]

This part:

(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
HAVING Count(D.MasterID) = 1)

Gives an error that one of the columns is not part of an aggregate function.

Ivan

 

[Ivan Debono]

I guess we can eliminate the master table complete and concentrate on the
details table only. Imagine the details table is an order_details table,
with columns:
id (autonumber)
order_id
product_id

The query should return all orders where count(product_id)>1 and distinct
count(product_id) = 1.

Ivan

 

[John Spencer]

I think this will fix that.

SELECT Master.Master_ID
FROM Master INNER JOIN Details
ON Master.Master_ID = Details.Master_ID
WHERE Master.Master_ID IN
(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
GROUP BY D.Master_ID
HAVING Count(D.Master_ID) = 1)
GROUP BY Master.Master_ID
HAVING Count(Master.Master_ID) > 1

 

[John Spencer]

QueryA:
SELECT Distinct Order_ID, ProductID
FROM Order_Details

QueryB:
SELECT Order_ID, Count(Product_ID) as CountProducts
FROM QueryA
GROUP BY Order_ID
HAVING Count(Product_ID) = 1

QueryC:
SELECT Order_Details.Order_ID
FROM QueryB INNER JOIN Order_Details
ON QueryB.Order_ID = Order_Details.Order_ID
Group By Order_Details.Order_ID
Having Count(Order_Details.Order_ID) > 1

 

[Ivan Debono]

You beat me to it for a few minutes!!! This is what I was trying to do. It
works!

Thanks,
Ivan