Questions tagged [union]
Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
191
questions
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?
