Questions tagged [union]
Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
169
questions
0
votes
2answers
67 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 ...
0
votes
1answer
54 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
23 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
118 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
23 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
19 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
82 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
57 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
60 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
44 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
78 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
37 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
260 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
221 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
42 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
99 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
25 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
49 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
47 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
109 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
75 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
1k 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
337 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
418 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
1k 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
78 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?
1
vote
1answer
28 views
MySQL - Query Main Table Rows and All Children from it's rows on the same query
I have the view_product which contains what i need and i have many child tables for it.
Currently i have a query where i get everything in a single query (unions) and programmatically split and ...
0
votes
1answer
47 views
SQL Query: Column Name as Value
I have a Redshift table that looks like the following:
+----------------+---------------+------+
| FieldA | field1 |field2|
+----------------+---------------+------+
| ABC | ...
0
votes
1answer
273 views
Joining two tables with unrelated where clauses
I have two tables: (EMPAssignments) and (EMPloyees). Both have an employee id column.
EMPAssignments has columns for employeeid, assignment, and ftepercentage:
employeeid assignment ftepercentage
...
1
vote
1answer
71 views
How to make this query simpler
SELECT IH.InvoiceNo,
C.Name AS NameofCustomer,
IP.Amount AS CashAmount,
0 CreditNote,
0 ChequeAmount,
0 Credit,
IP.Amount AS SalesValue
FROM InvoiceHeader IH
LEFT JOIN ...
1
vote
0answers
118 views
Another form of WHERE NOT EXIST
I have two complex queries and I have to filter out the results from the second one, it looks something like this:
SELECT STH INTO #temp_table_1
FROM TABLE1,
TABLE2,
TABLE3
WHERE
TABLE1.ID = TABLE2....
1
vote
1answer
634 views
SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records
The following was one of the practice questions in the online course i took on udemy for SQL.
I have a table 'emp' and consider the below names in the query map to their corresponding columns in ...
0
votes
1answer
28 views
Output of UNION query [closed]
UNION
Suppose 2 SELECT queries are joined by UNION. Can I get output if the 2nd SELECT query has 0 rows and 1st SELECT query returned some rows?
1
vote
2answers
301 views
merge 3 mysql tables into one with no duplicates
I would like to merge 3 tables data, each have a number column in common,
but all the other data differs.
What I would like to achieve is selecting all of the data from the common column
and merging ...
1
vote
4answers
599 views
Check if two tables have the same structure (are compatible)
I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense ...
10
votes
2answers
1k views
Problem with union casting integer to ceiling(decimal)
I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.
The problem is that this query is generated by an external library, so I don'...
0
votes
1answer
1k views
Merge data with UNION ALL and INNER JOIN in MySQL
I try merge a data to one row. I have query:
(select t.`id`,
t.`coins_id`,
t.`first_coin`,
t.`second_coin`,
t.`price` as `price_old`,
null as `price_young`...
-1
votes
2answers
3k views
Select all columns with same name from different tables
I have 4 tables. In 3 of these, there are a column named ACTIVE_FL which can have a value of "Y" or "N".
How can I select, from all tables only the columns ACTIVE_FL which have as value "N" ?
I ...
0
votes
2answers
35 views
unmanaged data in union
Forgive me if this question already ask,not much of db guy here ,
here is what i tried,
select row_number() over (partition by name order by challanto_date) , *
from (
select
rma,
...
-1
votes
2answers
142 views
Select multiple rows with different criteria for each [closed]
I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this:
SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" ...
2
votes
2answers
670 views
SQL Server select records from header and body table
I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records.
Header
Id Group Name Value
1 1 ...
0
votes
2answers
617 views
select distinct -> join 3 tables
Hi i need to mix (more than join) three tables, but the id could be present or not on each table (Please see the example below).
I have spend a few days trying join and union but all time some data ...
0
votes
1answer
466 views
Is a UNION query equivalent with or without DISTINCT in subqueries?
Consider the following two queries.
SELECT Col1, Col2
FROM TblA
UNION
SELECT Col1, Col2
FROM TblB
and
SELECT DISTINCT Col1, Col2
FROM TblA
UNION
SELECT DISTINCT Col1, Col2
FROM TblB
These are ...
1
vote
2answers
1k views
MySQL UNION ALL Performance
I've got two subqueries that I'm unioning together. Both subqueries return in < 200 ms but when I union them I get a query that runs in about 6 seconds (union all). The first query returns about ...
0
votes
1answer
563 views
Why does the UNION operation require that the relations on which they are applied be union compatible?
With respect to the operations in relational algebra (not in SQL):
I'm having trouble understanding why 2 relations have to be union-compatible before the union operation can be applied on them. I ...
-1
votes
1answer
505 views
SQL: Understanding GROUPING SET
As far as I can understand, the purpose of a GROUPING SET is to combine summary rows with raw data rows. This can also be achieved by combining a table with GROUP BY summaries with the UNION clause, ...
1
vote
1answer
340 views
Union 2 Temp Tables
I am trying to create a union of 2 temp tables, but the query is only returning values from the first table.
From searching the only thing I can find is that you cannot refer to the same temp table ...
0
votes
1answer
54 views
Query optimization (from joining two tables)
Alright so I have this query which regularly shows up in mysql slow log:
(
SELECT user_visit_logs.date,user_visit_logs.user_id,invalid_hits,
unique_hits,non_unique_hits,...
