{"id":80,"date":"2013-10-03T15:03:41","date_gmt":"2013-10-03T19:03:41","guid":{"rendered":"http:\/\/access-diva.com\/blog\/?p=80"},"modified":"2018-07-04T19:35:09","modified_gmt":"2018-07-04T23:35:09","slug":"union-queries","status":"publish","type":"post","link":"https:\/\/access-diva.com\/blog\/?p=80","title":{"rendered":"UNION Queries"},"content":{"rendered":"

A UNION query takes data from two (or more) tables and\/or queries and makes it appear as if it is from one source, i.e…<\/p>\n

SELECT FieldName, FieldName
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName
\nFROM 3ndTableOrQueryName;<\/p>\n

A few points\u2026<\/strong><\/p>\n

1.<\/strong> UNION queries are READ<\/strong> ONLY<\/strong>, no edits allowed!<\/span><\/span><\/p>\n

2.<\/strong> In the above rendition there can be no duplication of the rows returned. If any duplicate rows are specifically required to be returned, a UNION ALL<\/strong> operation should be used.\u00a0 However, I rarely find a reason to want duplicate rows in a UNION query.<\/p>\n

3.<\/strong> These queries can<\/strong> *run* slowly so use them sparingly.<\/p>\n

4.<\/strong> The Column Count<\/strong> must be the same for each query.\u00a0 If they do not match up you can use Null <\/strong>in place of a field name as a placeholder, i.e…<\/p>\n

SELECT FieldName, FieldName
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, Null<\/strong>
\nFROM 2ndTableOrQueryName
\nUNION SELECT Null<\/strong>, FieldName
\nFROM 3ndTableOrQueryName;<\/p>\n

5.<\/strong> You can specify Column Names<\/strong>…<\/p>\n

\"Specify

Specify Column Names<\/p><\/div>\n

SELECT FieldName AS FirstName<\/strong>, FieldName AS LastName, <\/strong>FieldName\u00a0AS TypeID<\/strong>
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName, FieldName
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName, FieldName
\nFROM 3ndTableOrQueryName;<\/p>\n

You only need to do this for the first query as the following queries will adopt whatever Column Name(s) is specified in the first query.<\/p>\n

6.<\/strong> You can use ORDER BY<\/strong> in a UNION query, i.e…<\/p>\n

\"ORDER

ORDER BY LastName<\/p><\/div>\n

SELECT cpFirstName, cpLastName<\/strong>
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName
\nFROM 3ndTableOrQueryName
\nORDER BY cpLastName<\/strong>;<\/p>\n

When specifying the ORDER BY<\/strong>\u00a0you must use the FieldName from the first query as in the subsequent Tables and\/or Queries the Field Names may not match or exist.<\/p>\n

7.<\/strong> You can use Placeholders<\/strong> to *add* additional information (columns) to your query as shown in (5.), i.e…<\/p>\n

For numeric, because the Field will default to TEXT you must declare it as NUMERIC…<\/p>\n

SELECT FieldName, FieldName, Val(1) AS TypeID<\/strong>
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName, Val(2)
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName, Val(3)
\nFROM 3ndTableOrQueryName;<\/p>\n

…or for TEXT just type as usual…<\/p>\n

SELECT FieldName, FieldName, “M” AS ItemID<\/strong>
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName, “C”<\/strong>
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName, “S”<\/strong>
\nFROM 3ndTableOrQueryName;<\/p>\n

Now, when you run your SELECT query you can filter on the Placeholder<\/strong>.<\/p>\n

8.<\/strong> You can use IIf()<\/strong> Statements<\/strong> in your UNION query, however, because this type of query can run slowly, use sparingly…<\/p>\n

SELECT FieldName, FieldName, Val(1)
\nFROM 1stTableOrQueryName
\nUNION SELECT FieldName, FieldName, Val(2)
\nFROM 2ndTableOrQueryName
\nUNION SELECT FieldName, FieldName, IIf([cpChecked]=0,Val(3)<\/strong>
\nFROM 3ndTableOrQueryName;<\/p>\n

You’ll notice the Field *cpChecked<\/strong>* is not in the query itself.\u00a0 It does not have to be, it only needs to be in the Table or Query used.<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

A UNION query takes data from two (or more) tables and\/or queries and makes it appear as if it is from one source, i.e…<\/p>\n

SELECT FieldName, FieldName FROM 1stTableOrQueryName UNION SELECT FieldName, FieldName FROM 2ndTableOrQueryName UNION SELECT FieldName, FieldName FROM 3ndTableOrQueryName;<\/p>\n

A few points\u2026<\/p>\n

1. UNION queries are READ ONLY, no edits allowed!<\/p>\n

2. In […]<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72,55],"tags":[18],"class_list":["post-80","post","type-post","status-publish","format-standard","hentry","category-queries","category-union","tag-queries","odd"],"_links":{"self":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/80","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=80"}],"version-history":[{"count":12,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/80\/revisions"}],"predecessor-version":[{"id":442,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/80\/revisions\/442"}],"wp:attachment":[{"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=80"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=80"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/access-diva.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=80"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}