I run a query and get back four rows. I run another query and I get back four rows. I want to get the eight rows at once, so I unioned them and I got...four rows. Does that make sense?
I have health care providers in pending tables (tblPendingXXX) and health care providers in non-pending tables (tblXXX). The providers have a fax number which is one of the columns I am selecting. So if I run the query against the pending tables I get four rows; one of them has a unique fax number and three of them have the same fax number. I am also selecting a hard-coded "pending" as one of the columns.
It just so happens (I am finding out if this is bad data) that the same provider also exists in the non-pending table. So when I select him there I get the same four rows but I am selecting a hard-coded "edit" as one of the columns. (edit and pending will be links on the grid when it is rendered on the web page).
So far we have four pending rows and four edit rows.
When I run these queries together with a UNION, I get four rows. Fax aaa and edit, fax bbb and edit, fax aaa and pending, fax bbb and pending. Which I don't quite understand. Why do they return 8 rows total separately, but 4 rows total when unioned?