4+0 = 2 in Union query. Data lost.

[Phil]

Imagine a fairly basic select query. It joins 7 tables together. One
of those tables has two identical fields, say primary_licensor and
secondary_licensor.

This query joins one of these tables to primary_licensor, and results in
4 records. I call this primary_licensor_query.

I change this query just by changing that join from primary_licensor to
secondary_licensor, which results in zero records found. I call this
secondary_licensor_query.

I cut and paste the SQL for each of these two queries into a third
query, seperated by the word "union". I call this all_licensor_query.

This SHOULD result in a query that returns 4 records + 0 records for a
total of 4 records.

It returns two records. WTF?

I will post it, but it has a couple of ugly iff statements. Note that I
can delete the word "union" and everything after it, and it returns 4
records. Please help.

Phil

SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id =
item.pri_licensor_id) ON item_types.type_id = item.item_type) INNER JOIN
invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8
Or (invoice_h.status)=9) AND ((licensor.labels) Like "Eric*") AND
((item.old_sku)="4494060"))
union
SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id
= customer.territory_id) ON licensor.licensor_id = item.sec_licensor_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8
Or (invoice_h.status)=9) AND ((licensor.labels) Like "eric*") AND
((item.old_sku)="4494060"));

 

[david epsom dot com dot au]

This SHOULD result in a query that returns 4 records + 0 records total of

4 records.

It returns two records. WTF?

You want UNION ALL

If you use just UNION, it does a 'group by'. Only DISTINCT
records are shown.

(david)

Imagine a fairly basic select query. It joins 7 tables together. One of
those tables has two identical fields, say primary_licensor and
secondary_licensor.

This query joins one of these tables to primary_licensor, and results in 4
records. I call this primary_licensor_query.

I change this query just by changing that join from primary_licensor to
secondary_licensor, which results in zero records found. I call this
secondary_licensor_query.

I cut and paste the SQL for each of these two queries into a third query,
seperated by the word "union". I call this all_licensor_query.

This SHOULD result in a query that returns 4 records + 0 records for a
total of 4 records.

It returns two records. WTF?

I will post it, but it has a couple of ugly iff statements. Note that I
can delete the word "union" and everything after it, and it returns 4
records. Please help.

Phil

SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id = item.pri_licensor_id)
ON item_types.type_id = item.item_type) INNER JOIN invoice_d ON
item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8 Or
(invoice_h.status)=9) AND ((licensor.labels) Like "Eric*") AND
((item.old_sku)="4494060"))
union
SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id =
customer.territory_id) ON licensor.licensor_id = item.sec_licensor_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8 Or
(invoice_h.status)=9) AND ((licensor.labels) Like "eric*") AND
((item.old_sku)="4494060"));

 

[Allen Browne]

Phil, I don't see anything wrong with the statement.

You could try a UNION ALL so it does not try to de-dupe the results, though
that should not make a difference.

The WHERE clause is correctly bracketed for AND and OR. Some of the sources
are queries, so I guess it is possible that there is a data type mismatch
happening here somewhere that would require typecasting:
http://allenbrowne.com/ser-45.html

The JOINs are all inner joins. Access (JET) has some problems with outer
joins, but they won't be relevant. Details:
http://allenbrowne.com/bug-10.html

The bracketing in the FROM clause looks a little unusual but again I don't
see a problem there.

Hopefully you will get other comments too if someone can see a pitfall.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Imagine a fairly basic select query. It joins 7 tables together. One of
those tables has two identical fields, say primary_licensor and
secondary_licensor.

This query joins one of these tables to primary_licensor, and results in 4
records. I call this primary_licensor_query.

I change this query just by changing that join from primary_licensor to
secondary_licensor, which results in zero records found. I call this
secondary_licensor_query.

I cut and paste the SQL for each of these two queries into a third query,
seperated by the word "union". I call this all_licensor_query.

This SHOULD result in a query that returns 4 records + 0 records for a
total of 4 records.

It returns two records. WTF?

I will post it, but it has a couple of ugly iff statements. Note that I
can delete the word "union" and everything after it, and it returns 4
records. Please help.

Phil

SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM territory INNER JOIN (customer INNER JOIN (((item_types INNER JOIN
(licensor INNER JOIN item ON licensor.licensor_id = item.pri_licensor_id)
ON item_types.type_id = item.item_type) INNER JOIN invoice_d ON
item.item_id = invoice_d.item_id) INNER JOIN invoice_h ON
invoice_d.invoice_id = invoice_h.invoice_id) ON customer.customer_id =
invoice_h.customer_id) ON territory.territory_id = customer.territory_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8 Or
(invoice_h.status)=9) AND ((licensor.labels) Like "Eric*") AND
((item.old_sku)="4494060"))
union
SELECT invoice_h.invoice_date, invoice_h.status, licensor.labels,
item_types.name, territory.name, IIf(territory!name="Foreign_1" Or
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS
[Foreign], invoice_d!ship_qty*invoice_d!price AS Ext_price,
invoice_d.ship_qty, invoice_d.item_id, item.old_sku
FROM licensor INNER JOIN (territory INNER JOIN (customer INNER JOIN
(((item_types INNER JOIN item ON item_types.type_id = item.item_type)
INNER JOIN invoice_d ON item.item_id = invoice_d.item_id) INNER JOIN
invoice_h ON invoice_d.invoice_id = invoice_h.invoice_id) ON
customer.customer_id = invoice_h.customer_id) ON territory.territory_id =
customer.territory_id) ON licensor.licensor_id = item.sec_licensor_id
WHERE (((invoice_h.invoice_date)=#5/2/2006#) AND ((invoice_h.status)=8 Or
(invoice_h.status)=9) AND ((licensor.labels) Like "eric*") AND
((item.old_sku)="4494060"));

 

[Phil]

Nope, actually all tables straight from a MYSQL database. No queries.
However, "union all" did work. Another example out of way too many
where Access does NOT do what it is supposed to do.

Thank you both for your help.

 

[david epsom dot com dot au]

Nope, actually all tables straight from a MYSQL database. No queries.
However, "union all" did work. Another example out of way too many where
Access does NOT do what it is supposed to do.

http://www.1keydata.com/sql/sqlunionall.html
http://www.w3schools.com/sql/sql_union.asp
http://www.techonthenet.com/sql/union_all.php
http://dev.mysql.com/doc/refman/5.0/en/union.html

 

[Allen Browne]

David, thanks for posting the links that explain the difference between
UNION and UNION ALL.

While I understand that difference, what I don't understand is why a query
that returns 4 records UNIONed with a query that returns 0 records should
return only 2 records. It seems to me that the de-duplication between 4
record and 0 records should return all 4. Perhaps the 4 themselves contained
duplicates?

Have I missed something here?

 

[david epsom dot com dot au]

Absent any other information, we have been told that the 4
records contain duplicates, because there are only 2 after
doing a union query.

I know Access/Jet has bugs. I'll still take 20 to 1 that any
unknown error is operator error.


(david)

 

[Gary Walter]

David, thanks for posting the links that explain the difference between
UNION and UNION ALL.

While I understand that difference, what I don't understand is why a query
that returns 4 records UNIONed with a query that returns 0 records should
return only 2 records. It seems to me that the de-duplication between 4
record and 0 records should return all 4. Perhaps the 4 themselves
contained duplicates?

Have I missed something here?

you have to be correct Allen

simple table t1 with 18 records,
some dups.....

SELECT t1.str1, t1.str2
FROM t1
UNION
SELECT t1.str1, t1.str1
FROM t1
WHERE 0=1;

returns 5 records

 

[Phil]

Nope. While the data might have had the same values, there were four
distinct records being returned.

 

[Phil]

I will be damned if I am gonna say I am perfect, (far from it), but the
actual data itself is coming pretty much from a single table,
(invoice_d). I can see, by directly opening the table and looking
around, that there are four distinct records which meet the criteria of
that first query. I do not have an explanation why the union makes two
of them disappear, but it does.

Phil

 

[Gary Walter]

simple test:

just add "DISTINCT" to start of
first query:

SELECT DISTINCT
invoice_h.invoice_date,
invoice_h.status,
licensor.labels,
item_types.name,
territory.name,
IIf(territory.[name]="Foreign_1"
Or territory.[name]="Foreign_2"
Or territory.[name]="Foreign_3","YES","NO") AS [Foreign],
invoice_d.ship_qty*invoice_d.price AS Ext_price,
invoice_d.ship_qty,
invoice_d.item_id,
item.old_sku
FROM
territory
INNER JOIN
(customer
INNER JOIN
(((
item_types
INNER JOIN
(licensor
INNER JOIN
item
ON
licensor.licensor_id = item.pri_licensor_id)
ON
item_types.type_id = item.item_type)
INNER JOIN
invoice_d
ON
item.item_id = invoice_d.item_id)
INNER JOIN
invoice_h
ON
invoice_d.invoice_id = invoice_h.invoice_id)
ON
customer.customer_id = invoice_h.customer_id)
ON
territory.territory_id = customer.territory_id
WHERE
(((invoice_h.invoice_date)=#5/2/2006#)
AND
((invoice_h.status)=8
Or
(invoice_h.status)=9)
AND
((licensor.labels) Like "Eric*")
AND
((item.old_sku)="4494060"))

do you get 4 or 2?

 

[Rick Brandt]

Nope. While the data might have had the same values, there were four
distinct records being returned.

A UNION query eliminates duplicates in the combination of fields. It doesn't
matter if there were four distinct records. If the fields used in the UNION
have the same data then they are considered duplicates and half of them will be
tossed in the output.

 

[david epsom dot com dot au]

Yes, that's why I included the url's documenting the behaviour
of Union and Union all.

(david)

 

[AccessVandal via AccessMonster.com]

Phil,

Look at these,

item_types.name, territory.name, IIf(territory!name="Foreign_1" Or "
territory!name="Foreign_2" Or territory!name="Foreign_3","YES","NO") AS

The "Name" is a reserve word for Access, rename the columns/fields
for the tables.