Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

1
vote
1answer
15 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
18 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
16 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
39 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
0answers
8 views

get the turnover , income, total expenses from my database and group them by year-month

I have 4 tables : Products - to record products spending - to record expenses directv - to record subscription payments to TV channels sales - to record sales Products has 4 columns : idProd is ...
0
votes
1answer
39 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
22 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
48 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
43 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 ...
2
votes
1answer
52 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
71 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 ...
0
votes
0answers
12 views

Fastest MySQL pagination and ORDER from a huge UNION ALL [duplicate]

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 ...
2
votes
1answer
142 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
48 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
27 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
32 views

SQL Query: Column Name as Value

I have a Redshift table that looks like the following: +----------------+---------------+------+ | FieldA | field1 |field2| +----------------+---------------+------+ | ABC | ...
0
votes
1answer
45 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
68 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
32 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
65 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
25 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
64 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
78 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
739 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
253 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
1k 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
74 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
496 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
169 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
324 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
658 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
384 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
405 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
128 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
50 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,...
1
vote
1answer
18 views

Data from more than 1 query - possible union needed?

I have a query (Query A) that pulls data from my Customer table. I have another query (Query B) that pulls data from a list of records associated with various customers. - Every customer on Query B ...
1
vote
1answer
264 views

Change DEPENDENT SUBQUERY to JOIN

I have the following query. It calculates the best match for each row in flight based on the the data in airport SELECT flight.id, ( SELECT id FROM airport ...
0
votes
2answers
3k views

joining tables without duplicate rows / NULL values [closed]

so I have multiple tables which I have to merge when query but both join and union doesn't seems to achieve what my object is Union: mysql> SELECT NAME, currentstatus, NULL AS username, NULL ...
3
votes
0answers
806 views

Speed slow down on a UNION ALL view

This is my DB architecture: a 3.000.000 records product (memory) table that must be shared by all databases a x (say 1) records product table specific for each single database a view in each ...
0
votes
0answers
198 views

MySQL use join instead of union

I'm have two table right here. One is the holdings table. Another one is stats table. The holdings looks like this: DATE_ TICKER WEIGHT 2016-05 APPL 0.1 2016-05 GOOG 0.1 ...
2
votes
2answers
36 views

Summing “Other” remaining after Top 5

I have the query below which returns the top 5 sum per brokerage SELECT * FROM (SELECT b.brokerage_name AS Brokerage, SUM(c.cost) AS Cost FROM (tblquotesnew q ...
0
votes
2answers
203 views

SELECT from tableA if not in tableC

I have a view, which selects data from TableA (TableA will be decommissioned once we migrated it all). We are creating a new table, called TABLEC, where we'll be migrating all the data from TableA to ...
-1
votes
1answer
145 views

T-SQL Query with multiple union statements providing error of unequal expressions

With running the entire T-SQL code below it seems I am getting the following error below. Troubleshoot Steps: Run each block of code before each "UNION" (Each block executes fine and returns ...
0
votes
2answers
842 views

SQL | How to sum in same table but referring to different cell values

As an example, I've a table like this: CREATE TABLE Sample (`id` int, `productName` varchar(7), `description` varchar(55), `quantity`int(10)) ; INSERT INTO Sample (`id`, `productName`, `...
14
votes
1answer
658 views

How can I prevent UNPIVOT from being transformed into UNION ALL?

I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot ...
5
votes
3answers
660 views

Union implemented with Hash Match operator

I was reviewing the SQL Server physical operators listed on TechNet (don't judge, you know you've done it) and read that the Hash Match physical operator is sometimes used to implement the UNION ...
2
votes
0answers
44 views

different ways to optimise union all on a particular staging process

quite often while tuning queries and stored procedures I come across the situation where I have a number of different types of the same object, each type has different characteristics but all of them ...
1
vote
1answer
59 views

Moving mysql union query to postgresql

I am trying to move a mysql query to postgresql and I have a problem with the union operation, this is the query: SELECT "user_firstname" , "user_lastname" , "...