FAQ Database Discussion Community


SQL Server - UNION ALL

sql,sql-server,union
I'm new to SQL development and I need to do UNION on two select statements. Below is a sample query. The Join tables & conditions, where criteria, columns names and everything is the same in both the select statements except the the primary tables after the FROM clause. I just...

My sql statement has two parts attached with 'union all'. I want the second part to exceecute only when some condition is true

mysql,sql-server,db2,union
my query is too big to paste here and refers to at least 15 tables, so i am pasting a small example to explain my problem SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City; i want the union all...

SQL Adding unmatched Join results to output

sql,sql-server,union
Maybe I am approaching the entire problem wrong - or inefficiently. Essentially, I am trying to combine two views of data, one of them a log table, based upon 2 criteria: RoomName field match Timestamp matches vw_FusionRVDB_Schedule (RoomName, StartTime, EndTime, Subject, etc) This contains the schedule of events for all...

Invalid column name 'CalanderDate'. when using a with and a union

sql,sql-server-2012,union
i have the following query : WITH Calender AS ( SELECT @FromDate AS CalanderDate UNION ALL SELECT CalanderDate + 1 FROM Calender WHERE CalanderDate + 1 <= @ToDate ) INSERT INTO #C SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) , case [dbo].[CalculateNumericWeekCode] (4, CalanderDate) when 1 then 'A' when 2 then 'B' when...

Convert UNION ALLs to JOINs

sql,sql-server,join,sql-server-2012,union
Is there a way to convert UNION ALL to JOIN and still get similar output. Here is an example query to illustrate: DECLARE @customerIdentifierId BIGINT SET @customerIdentifierId = 2 SELECT 1 AS Tag, NULL AS Parent, cust.CustomerId AS CustomerId, NULL AS CustomerIdentifierId, NULL AS OrderDetailId FROM Customer.CustomerIdentifier custIdent JOIN Customer.Customer...

How to get the values from 2 tables together in SQL

sql,union
I have 2 tables 1. emp_mst empcode empname 001 abc 002 def 2. leavetotal empcode leave 001 10 001 5 001 2 002 12 002 8 Now i am trying to get the empcode and empname from the emp_mst and the total for leave days from leavetotal.I have no idea...

LINQ equivalent of my SQL Query for UNION ALL

sql-server,linq,sql-order-by,union,equivalent
(SELECT * FROM SheetHay WHERE SheetStatus = 2) UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus = 1) UNION ALL (SELECT * FROM SheetHay WHERE SheetStatus = 0) UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus= 3) I get result set like this: I mean I get all '2' together, '0' together,...

Select from two databases - Union but retain all headers

mysql,union
I have searched for help on this but nothing quite fits what I'm after, though I'm sure it must have been asked before. Table 1 - table1 table1.date (date), table1.name (varchar(20)) Table 2 - table2 table2.date (date), table2.regnum (int(10)) I am trying to display a full list of the data...

UNION not ouputting correct combined data in SQL/SSRS server 2008

sql,reporting-services,union
I am a NEWBIE, self taught SQL creator. I have created a report that uses a series of queries to create 2 temp tables that need joined together. When I run the report in SQL, I actually get the first temp table as a result, then I get the combined...

SQL server Temp table with joins inside other select

sql-server,subquery,union,temp-tables,sql-server-2014-express
I have the following structure: Create @temp Select ...inser...into @temp where ... (select ... from @temp Join tblA where ... ) UNION (Select ... from @temp join tblB where ... ) After build above table I need to be able to perform WHERE, JOINS, ... Something like: Select ... from...

ZF2 Pagination does not work with Union

php,mysql,pagination,zend-framework2,union
I am using PHP with Zend Framework 2.3.3. I used Paginator for a Select with a Union. The code is like this: $where = new Where(); $where->like('value', "%$infoToSearch%"); $select = new Select(); $select->columns(array(new Expression('DISTINCT(id)'))); $select->from('products'); $select->where($where); $select2 = new Select(); $select2->columns(array(new Expression('DISTINCT(id)'))); $select2->from('products_archive'); $select2->where($where); $select->combine($select2); $paginatorAdapter = new DbSelect($select,...

Use of UNION when creating tables

mysql,union
Database-1 create table sample ( id INT, nm VARCHAR(10) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 UNION=(for tables from another databases); So, when we do union what actually it meance? Please explain, I am getting confusing for this type of UNION....

Latest date for each id, in a union table

mysql,union,union-all
I have a database, holding workers and three different tasks they can complete. Each task table holds which worker carried out this task, when, and how long it took. So the tables look as follows: Worker table: ------------------------- |worker_id | worker_name| |----------|------------| | | | Task tables: ------------------------------------ |task_id |...

SQL Query help using UNION or nested queries

mysql,sql,union
I am trying to execute a query but its not working out so well for me. I got these tables: create table spelare( Spnr char(13), Snamn varchar (20), primary key (Spnr) )engine=innodb; create table speltillfalle( starttid varchar (15), Tnamn varchar (20), Spnr char (13), resultat char (10), primary key (starttid,...

Optimization - is union the best way to go in this case?

sql-server,join,optimization,union
I have a dataset which is updated on a daily basis from an external data source (Example). This data is then combined with some other in-house data (Scale) and is formed to a new table. Example contains some figures that can be errornous at times. I register for which funds...

Optimization of view with union / cross join

sql,sql-server-2008,view,union,cross-join
I would like to ask about one specific view I have and potential optimization. Seems like quite a casual tables setup - Customers (~50.000 rows) and Users (~250 rows), while most users can access all the customers (infact, there is a different permission level on customers geographical location, but it...

Full Outer Join (left join union right join) MySQL multiple tables

php,mysql,join,union
I have looked at other related SO questions, but they couldn't click for me. I have these tables: http://i.stack.imgur.com/cEYVn.png In the end, I’d like to combine these 5 tables so everything from Location (maybe loc_type (int) replaced with loc_type_name), everything but loc_id from Hours (since it will be joined on...

MYSQL: UNION results between two tables where omitting records from first table if PK found in second table

mysql,sql,union
I have two tables products and product_edits which hold product information on the pricelist. My app works in a way that if user changes any product info in products table it inserts it into product_edits table... PRODUCTS table pk|code|name |description|price|.... ----------------------------------- 1 |QW1X|Product 1|... 2 |LW1X|Product 2|... 3 |DE1X|Product 3|......

ORDER BY multi-valued field in an Access query

sql,ms-access,union,multivalue
An intro about why I'm using multi-valued fields that you can skip if you want: I know that multi-valued fields are considered harmful, but I have a really good use for it. I have items that some are in Category A, some are in Category B, and some are in...

UPDATE and return some rows twice

sql,postgresql,sql-update,union,common-table-expression
I try to update and return rows. The problem is I use a nested select with UNION to get some rows twice and I want to get them returned twice. Example: Table: First_name | last_name | ready -----------+-----------+------ john | doe | false | smith | false jane | |...

Logical union of data from tables with diverging numbers and types of columns

sql,union
Consider a logical union (may not actually use the 'union' keyword) of the following three (or more) tables: TableA: common_key int, 'TableA' as tname', taba_col1 string, taba_col2 int TableB: common_key int, 'TableB' as tname', tabb_col1 double, tabb_col2 double, tabb_col3 string .. TableN: common_key int, 'TableN' as tname', tabn_col1 int, tabn_col2...

Square root union and Bit Shift

c,union,bit,shift
I found this code with which the square root is obtained what surprises me is the way it does, using a union and bit shifts this is the code: float sqrt3(const float x) { union { int i; float x; } u; u.x = x; u.i = (1<<29) + (u.i...

Aggregate example sets in Rapidminer based on a specific attribute

union,aggregation,rapidminer
I am a bit stuck with a simple task of aggregating two example sets using rapidminer and need an advise on a process and possible operators. I have two CSVs like: key A B C D E first 2 2 2 2 2 second 3 3 3 3 3 and...

Time complexity of union

algorithm,union,time-complexity,dijkstra
We have a directed graph G=(V,E) ,at which each edge (u, v) in E has a relative value r(u, v) in R and 0<=r(u, v) <= 1, that represents the reliability , at a communication channel, from the vertex u to the vertex v. Consider as r(u, v) the probability...

MySQL Merge Two SELECT Queries With CASE On One Table

mysql,select,join,merge,union
I want to merge two queries into one full query. I would need the two queries to group on one aggregate field (MFG). I would assume you use a JOIN but it is on the same table and with a CASE so I'm not sure how that could even work....

C# Linq , use DefaultIfEmpty to get default class values when using Union

c#,linq,union,defaultifempty
I need to return a set of MyClass based on a tuple value list. This tuple values are used to get the correct object from database. In order to avoid calling db many times, I'm trying to use a Union to build the query and then get all the data...

Implement a union all query with an inner join

mysql,sql,join,union
I have 2 table like this: // table1 +-----------------+-----------------+ | col1 | id | |-----------------+-----------------| +-----------------+-----------------+ | test | 1 | |-----------------+-----------------| | test | 2 | |-----------------+-----------------| | anything | 3 | |-----------------+-----------------| | anything | 4 | |-----------------+-----------------| // table2 +-----------------+-----------------+ | col1 | id | |-----------------+-----------------| +-----------------+-----------------+...

C pointers instead of union [closed]

c,pointers,union
I have an embedded system that should store different sets of results based on its state. One possible solution is to use a union: typedef struct { union { struct { double Val1; double Val2; } State1; struct { double Val3; } State2; }; } strBuf; strBuf Buf; Now I...

union and rollup in mysql

mysql,sql,database,union,rollup
I couldn't find what is causing this error, when I union headers with the actual data constructed using rollup to write them to a .csv file. CREATE TABLE `products` ( `id` int(11) default NULL, `item` varchar(100) default NULL, `value` int(11) default NULL ) ENGINE=MyISAM ; INSERT INTO `products` VALUES (1,'Mobiles',...

Need to replace union in the SQL Query

sql,database,oracle,union
I have to write a sql query For each product, determine how many times it was bought in 2010, if at all. The result should be tuples of the form (product name, num times). If a product was not purchased at all in 2010, the second attribute should have a...

Create an ORACLE query - View and union with default value

database,oracle,view,union
For a specific need, I would like to create a view by merging data from several tables and adding data by default. More details: I have 3 tables in my database: the table contracts (id_contract, name) the table portfolios (id_pfo, label) a linked table "contract_Linked_pfo" (id_contract, id_pfo, budget) For each...

MySQL Loop from two different Tables

php,mysql,union
i want to get values from two different tables. my problem is the table have no relation to each other but the "team_id" i have a "player" and a "player_stat" table. my result should look like: Playername (from the "player" table) Points: 8, Assists: 2, Fouls: 0 (from the "player_stat"...

Is there a way to display the table orgin for rows in a union

sql,sql-server,union
When I union n amount of tables is there a way to add a column to result set that says what table that row came from?

How to use MySQL UNION inside UNION ALL

mysql,sql,union,union-all
I am trying to execute this query: ( SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank FROM club_offer co WHERE co.id IN (31, 791, 360, 382) GROUP BY offerId UNION SELECT co.id as offerId, co.title, co.details, co.link, co.reference, co.rank FROM club_offer co WHERE co.id IN (869, 376, 201, 1246)...

Improve MySQL Union Statement

mysql,join,union
I have the following mySql statement, which I am sure can be made more efficient, I'm just not sure what would be the best way... the only difference is the equip_id that changes in the WHERE clause... ============== (SELECT `receipt_ts`, `driver_id`, `equip_id`, `pos_lon`, `pos_lat`, `pos_timestamp` FROM `log_messaging` WHERE `equip_id`='207' AND...

Mysql Union in different columns

mysql,sql,union,union-all,sql-subselect
I have the following query SELECT * FROM( (SELECT MAX(c.start_time) as start_1 FROM c1 c) UNION ALL (SELECT MAX(cc.created_at) as ccmax FROM cc1) ) as t I'd like to have the result in a table with 2 columns start_1 and cmax instead of the single column I get with all...

mysql Output data from two tables using most recent entry

php,mysql,sql,group,union
I have done a good search for a similar question, but with no joy. I think the answer to my question is based around DISTINCT, GROUP and UNION, but just can't put the together in any way which makes sense. I have 2 tables: users(id, username) activitylog(id, userid, usernamedisp, activity)...

SQL UNION only returning result from first SELECT

php,mysql,sql,pdo,union
I am attempting to perform a SQL call that fetches a row from one table and counts the records from another table. However, the call only returns results from the first SELECT. Code is as follows: $sql = "SELECT row_1 FROM table_1 WHERE user_id = :user_id UNION SELECT COUNT(row_2) as...

MySQL : Select three last item INNER JOIN [duplicate]

mysql,inner-join,union,var,multiple-tables
This question already has an answer here: Using LIMIT within GROUP BY to get N results per group? 11 answers Just have a tricky blockING with MySQL. I've 3 tables : TV | TV_ID | TV_name | ---------------------- | 1 | HBO | | 2 | BBC | |...

Three columns in two tables to four columns in one table

sql,sql-server,select,join,union
I'm trying to combine two tables, each has three columns and two of those are common. The income table has Location, date and Amt_recd, the expense table has Location, date and Amt_spent. I want to combine these two tables so there is one table with four columns, and any common...

Multiple SELECTS from different tables MySQL

mysql,database,select,join,union
I'm having two tables with the following structure: Table t1 ||My_id|| ||A1|| ||A2|| ||B1|| ||B2|| * neither of the above columns are unique Table t2 ||My_id|| ||Info|| * My_id is unique in t2 I would like to do a single query that can return the following: ||ABUnique|| ||Info|| Where: ABUnique...

how fast ist it in sql if I would select only the first row with four selects

sql,select,union
I'v a problem with look like the same as Union Select Only One Row But with little different. I'v four selects combined with the union statment. select top 1 value from ( select 1 as id, value from resource where rkey = 'a' union select 2 as id, value from...

Optimizing an union SQL query

mysql,pdo,query-optimization,union
I've got a big query that all contain same types I need for a feed that I list in my app. Now the problem is that this query isn't very fast. I'm thinking that if I limit each individual union it might speed it up a bit but I'm not...

Filter Duplicates in UNION with SQL Server

sql,sql-server,union
I am trying to remove the duplicate entries which are being caused by date values being different. I tried to use min(date) in group by but that's not allowed For example in getting back the following 2 rows when all I need is the 1st MasterCustomerId NewClubKeyId DateAssigned 000000201535 K18752...

Unique movies by UNION the results of two join, MYSQL

mysql,join,union
I ran the following query to UNION the result of two joins over the below 4 tables: SELECT member_CROWD.worker_Id, member_CROWD.Unreliable, Answer_CROWD.qId, Answer_CROWD.answer FROM member_CROWD JOIN `Answer_CROWD` ON member_CROWD.worker_Id = Answer_CROWD.worker_Id WHERE member_CROWD.Unreliable = '0' AND Answer_CROWD.qId = 'q8' GROUP BY Answer_CROWD.answer UNION SELECT member.mem_id, member.Unreliable, Answer.qId, Answer.answer FROM member JOIN...

How to find union of n vector?

c++,vector,union
I have a 2-D vector of hyperedges as well as an adjacency list. I have to find the union of hyperEdges[i].size() vectors, but I can only find the union of just two vectors. What improvement can I make to my code below to do this? I want to store the...

SQL UNION performance

sql,union,tableau,vertica
I am using three UNIONs in my sql query. The query takes a lot of time. Is there any other faster alternative to it? My database is Vertica and I've written the query in Tableau. The Select Query looks like - (select A,B,C from (select fun(X) as A, B,C,D from...

Adding Month-to-date and Year-to-date in a query using SQL Server 2012?

sql-server,database,date,subquery,union
so I'm trying to make a query that includes a daily sum of the amount from the first instance the database starts collecting data to the last available instance of that date (database collects data every hour). And while I have done this, now I have to make it show...

Can not order by when using UNION in mysql

php,mysql,sql,order,union
I have this query that is working if I do no do UNION: SELECT t1.offer_id, t1.cpv_id, t1.type, FROM `offer_cpv` t1 JOIN `offer` t2 ON t1.offer_id = t2.id JOIN `company` t3 ON t2.cmpid = t3.id WHERE t1.cpv_id = :cpvid AND t3.status IN (4, 6) GROUP BY t1.offer_id ORDER BY t1.type Key...

phpmyadmin mysql syntax for creating a query from multiple queries with different where clauses

inner-join,union,multi-select,mysqli-multi-query
I'm attempting to join two queries with different where statements, on phpmyadmin mysql, The following is the code i need help with: SELECT elec.`Property ID`, elec.`Year`, elec.`Month`, elec.`Electric Consumption`, gas.`Gas Consumption` FROM ( SELECT `Property ID`, `Year`, `Month`, `Year_ref`, Sum(`Electric Consumption`) AS 'Electric Consumption', Sum(`Electric Cost`) AS 'Electric Cost' FROM...

SPARQL: union on dbpedia changes birthdate

union,sparql,dbpedia
I'm facing a strange behavior by using "union" on the endpoint http://de.dbpedia.org/sparql. By using SELECT distinct * WHERE { { ?name dcterms:subject category-de:Haus_Liechtenstein. ?name rdf:type foaf:Person. ?name <http://dbpedia.org/ontology/birthDate> ?birthdate. Optional {?name dbpedia-owl:deathDate ?deathDate.} Optional {?name <http://de.dbpedia.org/property/gnd> ?gnd.} } filter (!bound(?deathDate)) } Order BY ASC (?birthdate) the birthdate of "Marie Kinsky"...

Sort by sub category using a union

sql,sql-server,database,group-by,union
Right now I'm sorting by 4 categories and displaying them. The first category, agriculture, needs to have tractors sorted at the top first and then the rest of the agriculture equipment. Tractors have the categoryid of 1108, 1109, 1110, 1111. This is the query that is being used now: SELECT...

Union and Count Collections in Neo4j

collections,neo4j,cypher,union,intersection
My simple database contains nodes of 'terms' and 'codes' linked to each other. There are two types of relationships. Relationships between 'terms' and 'codes' called :CODE and are undirected (or read in both directions equally). Relationships between 'terms' called :NT (which means narrow term) and are directed. I want to...

MySQL JOIN -> Get all data from right table even if there is no match

php,mysql,join,union
I have two tables, one with data (A) and other with categories (B) which is in one of those categories or uncategorized. I need to make simple stats, so I am using JOIN to get all data from table A and join it with table B. What I need is,...

Case statement to determine if I should union

sql-server,sql-server-2012,case,union
I currently want to do some sort of conditional union. Given the following example: SELECT age, name FROM users UNION SELECT 25 AS age, 'Betty' AS name Say I wanted to only union the second statement if the count of 'users' was >=2 , otherwise do not union the two....

Select from a subset of data

sql,sql-server,union
I am working on a query in MS SQL Server 2014 That basically is a lot of unions select x, y where a = b union select x, y where a = b union select x, y where a = b It works fine, however the where clauses are identical...

MemSQL geospatial union of polygons

union,geospatial,memsql
Will the new MemSQL version be able to calculate the union of polygons? For example calcuate the border of two adjacent or even intercepting polygons? +----+ | | | +-+--+ +--+-+ | | | +----+ to +----+ | | | +--+ +--+ | | | +----+ ...

how to write lots of small unions from C into Java

java,enums,union
I shall rewrite C code into Java. The core of original C code is a HW wrapper. In C we were using lots of unions for each HW register eg: typedef union RegIntStatus { u8 reg; struct { u8 bit0_abc:1; u8 bit1_cde:1; u8 bit2_xyz:1; u8 bit3_7_rsvd:5; } bits; } regABC;...

How to sort data in mysql UNION subquery?

mysql,sql-order-by,union
i need to select all vip from table and order them by rand, and then to add other data sorted by date. In first subquery everything is ok, but in second order by spa_date DESC does not work. I know that ORDER BY clauses inside UNION subqueries are ignored without...

MSSQL query tuning

php,sql-server,union
I have the following scenario: 1 Client have 2 products lists (one common, one private), those lists have many products, some products can appear in the two lists. If the product appear in the private list, I want to get it, otherwise I'll take the one in the common list....

Combine two queries in Oracle

sql,oracle,group,union
I have 2 queries to retrieve faultCount and responseCount as follows and it works fine. select count(*) as faultCount, COMP_IDENTIFIER from CORDYS_NCB_LOG where AUDIT_CONTEXT='FAULT' group by COMP_IDENTIFIER order by responseCount; select count(*) as responseCount, COMP_IDENTIFIER from CORDYS_NCB_LOG where AUDIT_CONTEXT='RESPONSE' group by COMP_IDENTIFIER order by responseCount; I need to join to...

I want to do a UNION with two SELECTS and put inside a table

sql,sql-server,table,select,union
I got this code so far and i would like to get them to UNION into one question but i cant get it to work. Ive been trying to do a new Select around cast and everything down to As Nvarchar ` DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<H3>Felaktiga rader</H3>'+...

Using MySQL aggregate functions and UNION operator together

mysql,union
This is the first time I'm trying UNION operator of MySQL but the following code is giving me an error of "undefined index: female". $sql = "(SELECT COUNT(gender) AS male FROM members WHERE gender='m') UNION (SELECT COUNT(gender) AS female FROM members WHERE gender='f') UNION (SELECT COUNT(gender) AS no_gender FROM members...

SQL Server: How to efficiently return the combined result set of two slightly different queries

sql-server,union,common-table-expression
I've been playing with this for a while and while there are some "brute force" approaches that technically work, I feel like I'm missing something that would be more elegant (and efficient). I have a single table that contains a history of events. For each event, there is a "source"...

How to use union in select clause?

sql,postgresql,select,union
I have two tables (created here for example) like: X1 Y1 a 1 b 2 c 3 d 4 and X2 Y2 a 5 m 6 n 7 b 4 And I want the output column: X Y1 y2 a 1 5 b 2 4 c 3 0 d 4...

How to write queries with concatenation and alias in Oracle?

sql,oracle,oracle11g,sql-order-by,union
I have two tables A and B with following schema :- Table A -> Two columns Principal (varchar) and LoginTime (Timestamp) Table B -> Two columns Username (varchar) and LoginTime (Timestamp) Now I have to run a query on both tables, union them and then order by username. So I...