GROUP BY on UNION ALL pulls way too many records 1

[ProtocolPirate]

The following query brings up about 45 records:

SELECT d.Date, d.Description, d.CheckNum
FROM Posting_Brea As p
INNER JOIN Daysheet As d ON d.AcntNumber=p.AcntNumber
INNER JOIN Daysheet AS d2 ON d2.RecID=d.PayID AND d2.ChargeNum=p.ChargeNumber
WHERE d.PymtLo>0 AND d.PayID>0 AND d.Description NOT LIKE '%@%' AND PrimIns='ETNA' OR SecIns='ETNA'

UNION ALL

SELECT d.Date, d.Description, d.CheckNum
FROM Posting_Brea As p
INNER JOIN Daysheet As d ON d.AcntNumber=p.AcntNumber
INNER JOIN Daysheet AS d2 ON d2.RecID=d.PayID AND d2.ChargeNum=p.ChargeNumber
WHERE d.PymtLo>0 AND d.PayID>0 AND d.Description NOT LIKE '%@%' AND PrimIns='ETNA' OR SecIns='ETNA'

But when I try to use this as a virtual table I end up with many thousands of records instead of fewer GROUP BY records:

SELECT a.Description
FROM (

SELECT d.Date, d.Description, d.CheckNum
FROM Posting_Brea As p
INNER JOIN Daysheet As d ON d.AcntNumber=p.AcntNumber
INNER JOIN Daysheet AS d2 ON d2.RecID=d.PayID AND d2.ChargeNum=p.ChargeNumber
WHERE d.PymtLo>0 AND d.PayID>0 AND d.Description NOT LIKE '%@%' AND PrimIns='ETNA' OR SecIns='ETNA'

UNION ALL

SELECT d.Date, d.Description, d.CheckNum
FROM Posting_Brea As p
INNER JOIN Daysheet As d ON d.AcntNumber=p.AcntNumber
INNER JOIN Daysheet AS d2 ON d2.RecID=d.PayID AND d2.ChargeNum=p.ChargeNumber
WHERE d.PymtLo>0 AND d.PayID>0 AND d.Description NOT LIKE '%@%' AND PrimIns='ETNA' OR SecIns='ETNA'

) As a GROUP BY a.Description
ORDER BY a.Description

Why is this bringing in thousands of records when the query above only brings in 45 records?

 

[SkipVought]

Why are you doing a UNION? Unless I am mistaken, both queries in the Union are identical. Whats the point?

ALSO, I'd use a set of parenthesis with the OR statement to assure exactly what criteria/criterion get OR'd.


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ProtocolPirate]

Woops, that was a typo, the second Posting table should be Posting_BeverlyHills, and there are eleven more posting tables like that which all have to be unioned together.

 

[SkipVought]

Would you like to begin from the beginning?

Sounds to me like you have tables labeled as DATA elements, not a particularly good design for a DB. Brea, Beverly Hills and eleven other tables ALL with the same structure? Your data ought to really be in ONE table with a field for the area, or city, whatever.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ProtocolPirate]

Lord, don't I know that, but I didn't write this hunk of junk and I don't have access to the code to alter it.

 

[BigRed1212]

If you can query them, can you query and insert 12 times to make a new table someplace with a better structure and then run your desired query off of the newly made table?

 

[ProtocolPirate]

That is an excellent idea! Thank you very much Big Red!