Questions tagged [union]

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

Filter by
Sorted by
Tagged with
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,...