Home > sql > How can I combine a union query with a max query?

How can I combine a union query with a max query?

January 11Hits:0
Advertisement

First of all, my data is gathered from the following tables:

  • tblProjects (fields ProjectID(key) and ProjectTitle)
  • tblDocuments (fields ProjectID(linked), DocID(key) and DocTitle)
  • tblRevisions (fields DocID(linked) and SubDate)

My goal is to combine the results of the below mentioned A (a UNION query) and B (a MAX query), and to make them work together (see C):

A) The union query:

SQL code:

    SELECT ProjectID,0 as [0/DocID],ProjectTitle     FROM tblProjects      UNION ALL SELECT ProjectID,DocID,DocTitle     FROM tblDocuments      ORDER BY ProjectID, [0/DocID]; 

RESULT:

    ProjectID    | 0/DocID | Project/DocTitle      ------------------------------------------     1            | 0       | Project 1             1            | 1       | Document 1.1          1            | 2       | Document 1.2     2            | 0       | Project 2     2            | 3       | Document 2.1     2            | 4       | Document 2.2 

B) The max query:

SQL code:

    SELECT tblDocuments.DocID, Max(tblRevisions.SubDate) AS MaxOfSubDate     FROM tblDocuments LEFT JOIN tblRevisions ON tblDocuments.DocID = tblRevisions.DocID     GROUP BY tblDocuments.DocID; 

RESULT:

    DocID    | MaxOfSubDate      -----------------------     1        | 22.02.2016             2        | 20.02.2016          3        | (blank)     4        | 21.02.2016 

C) Combining A and B:

(NB! I do not necessarily mean I want to compile the code from A and B into one single query. It could also be through a set of different queries: i.e. a third query which gathers data from A and B).

SQL code:

    ? I have absolutely no clue ? 

DESIRED RESULT:

    ProjectID    | 0/DocID | Project/DocTitle | MaxOfSubDate     ------------------------------------------------     1            | 0       | Project 1        | 0     1            | 1       | Document 1.1     | 22.02.2016      1            | 2       | Document 1.2     | 20.02.2016     2            | 0       | Project 2        | 0     2            | 3       | Document 2.1     | (blank)     2            | 4       | Document 2.2     | 21.02.2016 
Tags:sql, ms access

Related Articles

  • How can I combine a union query with a max query?January 11

    First of all, my data is gathered from the following tables: tblProjects (fields ProjectID(key) and ProjectTitle) tblDocuments (fields ProjectID(linked), DocID(key) and DocTitle) tblRevisions (fields DocID(linked) and SubDate) My goal is to combine t

  • Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTS

    Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTSJanuary 19

    Update: When I tried to provide a simple example which reproduces the issue I found the leveller in my database which introduces this issue. Therefore I did an update to this question in order to reflect this new finding. Below you will find scripts

  • Combining two columns/fields in PDO select query

    Combining two columns/fields in PDO select queryJanuary 30

    My PDO statement returns 3 fields of data and displays the 3 field in a 3 column table: I would like to adjust the code so the table displayed only has 2 columns. This first column should display the country's flag instead of the name. The flag will

  • How do I change a MySQL query into a Drupal query?May 21

    mysql_query("SELECT nid,title, type,created, v_api.ContentSum FROM `node` n LEFT JOIN ( SELECT content_id, SUM(CASE WHEN value=1 THEN 1 WHEN value=0 THEN -1 ELSE 0 END) AS ContentSum FROM votingapi_vote WHERE value_type = 'option' GROUP BY content_id

  • How to get query execution time and query result at the same time? July 26

    Is there any SQL query syntax that returns query execution time and query result at the same time? --------------Solutions------------- I agree with @DTest, how are your running your query? If you log directly into a MySQL terminal style and hand-run

  • changing a MySQL query to a drupal query any ideasDecember 7

    I'm trying to change the following query to a drupal db_query: SELECT first_name, SUBSTR(first_name,1,3) AS firstname_substr FROM civicrm_contact WHERE id != 'NULL' ; I have tried this, but its not working: $firstname_substr = $query->addExpression('

  • query comes in slow query when log_queries_not_using_indexes is enabledFebruary 27

    This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE a.sub_area_id = b.id AND b.area_id = c.id AND d.id = c.city_i

  • Non-Selective Query Exception on Task QueryApril 26

    I have the following three SOQL queries in a class that is being called by a Trigger. The Trigger counts the number of completed tasks and events for a given account. List<Account> accounts = [SELECT ID, activity_count__c FROM Account WHERE ID IN :a

  • Query Deadlock on Select Query May 1

    I have 2 queries deadlocking on each other. The first query is Select Top 1 NULL From [Table1] with (HOLDLOCK, TABLOCKX) Second Query is an insert query that only reads from [Table2] to put data into [Table3] The first query somehow has an exclusive

  • Converting Query to Pass-through query June 7

    Below query is row source of a form with a list box and some fields for search. the tables are linked in sql server 2008. it works fine. I am trying to convert the query to pass-through query as the records are qrowing up and access become slow. SELE

  • How do I control the fallback query after the original query returned cero posts?June 20

    I just figured out why WP developers expect you to create a separate 404.php page to control the output of that page. (and the silence about any attempt to do it otherwise) It's basically because they provided no good fallback (or I haven't found it?

  • Some doubts about how the main query and the custom query works in this custom theme?July 27

    I am pretty new in WordPress theme development and I'm not so into PHP (I came from Java and C#) and have the following situation in this custom theme As you can see in the homepage I first show a section (named Articoli in evidenza) containing the f

  • Is "orderby" in WP Meta Query conflicting with Meta Query?September 12

    I'm trying to write a meta query that looks for either events starting within the next year (event_start_date between now and 52 weeks), or events that are still going on (event_end_date between now and 52 weeks), and then sorts them together via the

  • Query Knowledge Article Type Query - if language is not always known ahead of timeNovember 30

    I have custom vf page which loads some fields from knowledge article type, this page currently contains hard coded language filter in soql query which limits from loading any other non English content. I may not know the knowledge article language ah

  • Android Debug Mode:Is it possible to view raw query or in-built query build by queryBuilder for countOf?January 29

    try { if (cashAction != null) { Dao<CashAction, Integer> dao = TestDB.getInstance(context).getCashActionDao(); long count = dao.queryBuilder().setCountOf(true).where() .eq(CashAction.LOCAL_DB_CASH_ACTION_ID, cashAction.getId()) .and() .isNull(CashAc

  • MDX: How do I combine/merge/union two Measures dimensions with calculated members?October 1

    I am trying to combine two dimensions in an MDX query. SELECT NON EMPTY ( [XXX].[YYY].children ) ON ROWS, NON EMPTY { [Measures].[Dimension1A], [Measures].[Dimension1B], [Measures].[Dimension2A], [Measures].[Dimension2B] } ON COLUMNS FROM [RePro] WHE

  • Combine fields from multiple tables in Access queryApril 9

    As an example, I have two queries in Access. The first outputs two columns: | Cust ID | Revenue 1 | | A | 5 | | B | 10 | The second query is similar: | Cust ID | Revenue 2 | | B | 8 | | C | 9 | I want to create a query that will combine these first t

  • query regarding combining an update and an insert query into a single query in mysqlJune 29

    i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data. I can use a select to get the old data, an insert to history and finally an update to c

  • How do i append UNION SQL Query with default search querySeptember 19

    Example: (SELECT ID, post_status, post_title, post_excerpt, post_content FROM wp_posts WHERE ((`post_title` LIKE '%2020%') OR (`post_excerpt` LIKE '%2020%') OR (`post_content` LIKE '%2020%')) AND (`post_status` = 'publish' ) ) UNION (SELECT ID, '', t

  • Combine privileged users array with my current queryOctober 14

    I have a custom page template with the following query. Isotope filers my categories and all this works fine. I want now to make this page only viewable by a few specific, currently logged in authors. Example: Posts are only visible to an author with

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.406 s.