Questions tagged [union]

Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.

Filter by
Sorted by
Tagged with
2
votes
2answers
27 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
0
votes
0answers
17 views

Select Type UNION RESULT doesn't appear in the output of Explain statement

EXPLAIN SELECT actor_id, (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id = der_1.actor_id LIMIT 1) FROM ( SELECT actor_id FROM sakila.actor LIMIT 5 ) AS der_1 UNION ALL SELECT film_id, (...
0
votes
0answers
15 views

Save multiple dynamic filter for aggregation in Postgresql

What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data? Lets assume I have a table called t_filter which ...
0
votes
0answers
14 views

Single table with many null fields or two tables used with union? [duplicate]

I am designing database for my small project and I've hit a "good design practice" wall ;) I need to add records that will have one of two structures, containing different set of information (10-20 ...
0
votes
1answer
37 views

Select from other table if exist

hi I want to union 2 tables but I am not sure if the table is exits this is my query: select a from table union select case when exists(select a from table1) then (select a from table1) else (...
0
votes
1answer
81 views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
0
votes
1answer
36 views

JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas. Basically, what I want to do seems like a UNION where records are "collapsed" in ...
1
vote
2answers
133 views

Union probably not right for this [closed]

I have a table witrh 3 columns: id price created_at 1 0 ts 2 5 ts 3 0 ts 4 3 ts I'd like to have a result table that contains 2 rows, like this: id ...
1
vote
1answer
25 views

Caching derived dataset automatically in SQL Server

I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there ...
0
votes
0answers
31 views

Why does UNION fail here?

I have a problem that I can't seem to figure out how to fix. What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error An error occurred in ...
0
votes
1answer
28 views

How do I join on a timestamp and include NULL entries?

I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in ...
0
votes
0answers
28 views

SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)? [duplicate]

Let's assume we have following query (greatly simplified, of course): SELECT * FROM MyTable Where A or B The query takes about 30 seconds to run When I rewrite the query as SELECT DISTINCT * FROM ...
0
votes
1answer
70 views

Bag union operation in relational algebra

I have difficulty in understanding how does the bag union operates in relational algebra? Here, 1) has a schema (X, Y, A) 2) (Y, A) 3) (X, A) 4) (Y, A) so how will output look like after union ...
0
votes
1answer
25 views

MySQL COUNT based on a condition [duplicate]

I have a MariaDB database, in which I have this table: ID | Item Name | Item replaced | ----------------------------------------- 0 | Item 1 | Replaced 1 | Item 2 | 2 | Item 3 | ...
8
votes
1answer
154 views

Microsoft SQL Server 2014 Nested From Query in Cross-Apply

When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances. Bug reported to Azure Feedback Forum: https://feedback.azure....
1
vote
2answers
62 views

Alternative to slow [LEFT JOIN + OR] without UNION?

I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables. The query using only left joins ...
0
votes
0answers
30 views

Union the same table. Optimize query

I'm coming with a question about merging two data sets. I am using STI (single table inheritance) in the products table, so one column defines what type of product the specific row stores. The ...
1
vote
1answer
62 views

Union from Multiple Tables in many databases and group results in batches of n

First post in dba, so please be gentle. I have a project I am working through and have 4+ SQL Server databases to work with. These DBs contain 2 (or more tables) I wish to UNION the results from. ...
1
vote
1answer
33 views

SQL db query using UNION and Left Join

I have 6 tables (t1, t2, t3, ...,t6 in the same SQL Server 2008 R2 database. I wanted to group them into two. Group 1 (TG1) includes tables 1, 2 and 3. Group 1 (TG1) includes tables 4, 5 and six. I ...
0
votes
0answers
12 views

I'm trying to order a table with 3 columns as if the columns were merged into 1 such that it would sort as 3 separate entries

I've hit a somewhat unique obstacle with my current project. Below is a mock MySQL table to better describe the issue I'm trying to solve. mysql> describe `minimized_event_instructions`; +---------...
0
votes
0answers
19 views

How to fetch different record when nothing matches in where conditions in the same query?

I am using ORACLE 12C database. There are two components - Agreement and Case. Agreement related information stores on Agreement_line and Object Table. Common column between these tables are ...
0
votes
1answer
52 views

Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year?

My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the ...
0
votes
2answers
131 views

How to sort rows after union

I have two tables that I want to combine. I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the ...
1
vote
1answer
4k views

Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float

I created some select statements which run without any problem in SQL Server. Then I joined them together with UNION and got this error message> Msg 8114, Level 16, State 5, Line 1 Error ...
0
votes
0answers
80 views

Does MySQL Lazy Union really work?

Following query is an example of Lazy Union: SELECT GREATEST(@found := -1, userId) AS userId FROM user WHERE userId = 1 UNION ALL SELECT userId FROM user WHERE userNumber = 1 AND @found IS NULL UNION ...
-2
votes
1answer
3k views

syntax error at or near “AS” [closed]

I have a program that takes in one or more than one table names, from a list stored in a reference table and runs a SELECT query on it. so I retrieve the table names and insert it into the SELECT ...
3
votes
2answers
34 views

Optimizing PostgreSQL where clause with OR

I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
1
vote
1answer
69 views

Get the combined row count by month from 3 tables

Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below: CREATE TABLE ...
0
votes
1answer
485 views

Will too many tables slow Mysql down?

I'm making a database which should be able to handle over 1,000,000,000 rows. in order to optimize it's performance i've decided to split tables. so instead of having 1,000,000,000 entries in one ...
-3
votes
1answer
60 views

Print Two Different Columns with Union All- SQL

I have an SQL statement like this, SELECT * FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"), (select "Doe" as LastName union all select "Queen" union all ...
0
votes
0answers
20 views

Combine multiple queries that each require a row of input values

I'm looking for a way to combine multiple queries that each need two values. For example, I can look up a patient's age and weight as follows: select age , weight from patients join ...
0
votes
1answer
262 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
1
vote
1answer
48 views

Handle 4 tables and to show 4 columns as result

i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns: I want to show this 4 rows, each from a different table. EDIT: Each company belongs to a Sector. ...
1
vote
2answers
88 views

MySQL Select Union; Filter by Shared Relation?

Currently, I have 3 tables (see structure below), speakers, venues, and events. I'm trying to do a relevance search on the names of the speakers and venues. However, on the Search Results page, if the ...
0
votes
2answers
41 views

MYSQL search for each name in 2 columns and add values associated for each player

I have a database "game_results". It contains the following: PlayerID (int not null primary key auto increment) player1 (varchar) p1destroyed (int) player2 (varchar) p2destroyed (int) winner (...
2
votes
1answer
501 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
2
votes
1answer
448 views

Table Locking When using Unions

When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
1
vote
2answers
54 views

Remove multiple joins to same table with where

I am stucked at below situation. Need expert advice. I have 3 tables, login_details,deleted_login_details and custom_fields as below. login_details structure = deleted_login_details custom-fields have ...
1
vote
1answer
185 views

Oracle Subselect Union Multiple Column

I want to select and count all cats and dogs at one location; location | cats | dogs | new york | 150 | 66 | paris | 34 | 12 | What I've got so far: SELECT cats,dogs FROM (select ...
0
votes
1answer
33 views

MySQL, comparison between, tables with not all records common, on 2 time intervals

I want to make comparisons on the volume of sales, turnover and margin of all product sales between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31). for example: Product ...
1
vote
0answers
70 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
0
votes
1answer
49 views

Selective union possible?

Is there any way to make a UNION consider only specific columns when checking for duplicity? The query below returns all of the columns that I need but, due to slight differences in some of the ...
0
votes
1answer
466 views

join two tables, include one row from the parent table for each primary key

I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I ...
1
vote
1answer
167 views

Simplifying the “repetitive” code of a query involving UNION ALL, WHERE and LIMIT

How can I simplify the code of the query shown below? Requirements are always the same as I describe as follows: (select * from questions where points = 3 and type = 1 order by rand() asc limit 6) ...
0
votes
1answer
5k views

Execute UNION and after LEFT JOIN in MySQL

I have 3 tables cdc_vw_Marbetes_Catalogo_Exis tblMarbetesAdicionales tblMarbetesCongeladas First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales. (SELECT ...
1
vote
1answer
14 views

union only dispalying the first query

First time posting in Stack Exchange so excuse any faux pas (well let me know them of course). I'm using union for the first time on an already Frankenstein'ed query I've pull together so I'm looking ...
3
votes
1answer
526 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
2
votes
1answer
644 views

Optimze performance for LIMIT / OFFEST on collected result of multiple queries

I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly: SELECT 1 : ~5 % rows ...
3
votes
1answer
2k views

Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW

Let's say we have a schema with table 'month' which have the current information and table 'year' which have the historic information. Each table has around 65,000 rows. This schema exists in several ...
0
votes
2answers
235 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?