The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [join]

An SQL join clause combines records from two or more tables or views.

Filter by
Sorted by
Tagged with
-3
votes
0answers
21 views

How can I create multiple rows from one table in mySQL?

I want to merge different data from one WordPress DataBase table into multibple row. Example jsFiddle:http://sqlfiddle.com/#!9/2ed83c/10 WordPress ERD: https://codex.wordpress.org/images/2/25/WP4.4....
-4
votes
1answer
16 views

MySQL returns more than one row with AND operator match

I have two tables 1:n relationship, customer to orders the schema is in the http://sqlfiddle.com/#!9/a6842e/1 I have created. The key in customer is composite id and type. I am trying to get the ...
0
votes
0answers
14 views

Is it possible to join on column placeholders?

I have a query below in which i was wondering whether it is possible to join on my placeholders and if so are my joins in my CTE's correct? WITH DCM AS (SELECT date, placement as creative, SUM(...
0
votes
1answer
18 views

group by with joins

So I have 2 tables with identical structures that represent data from 2 vendors.....I would have set this up in one table but I lost the argument with the data guy...anyway....keep getting an error ...
2
votes
1answer
22 views

Select IDS where all values can't be found in another table

Hey I'm pretty new to SQL and I've come up with a problem that I haven't been able to solve I'm trying to make a query to bring up ID's that have ALL null values and are located in another table. DB ...
6
votes
1answer
420 views

Replace long GROUP BY list with a subquery

This is a repost of my question on Stack Overflow. They suggested to ask it here: I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should ...
0
votes
0answers
14 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 ...
1
vote
0answers
15 views

Understanding PostgreSQL Execution Plans

I'm trying to understand the motivation of PostgreSQL 12 query planner here. Database Structure CREATE TABLE subjects ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, ); CREATE TABLE ...
6
votes
2answers
136 views

Why selecting rowversion using outer join never returns null

Using SQL Server 2014. Given table with rowversion column, other table to join with and a select like this: CREATE TABLE dbo.FooTable( [Id] [int] IDENTITY(1,1) NOT NULL, [RowVersion] [...
2
votes
0answers
26 views

Why can't outer join simplification happen for embedding outer join?

The MySQL documentation here suggests the following: Sometimes the optimizer succeeds in replacing an embedded outer join operation, but cannot convert the embedding outer join. The following query:...
0
votes
1answer
52 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
0
votes
0answers
19 views

How does MySQL optimizer compute join cost when indexed?

So, The standard formula in theory is C(R1.R2) = C(R1) + T(R1)*C(R2) where, C(R1.R2) - Cost of joining two relations (tables) R1 and R2 C(R1) - Cost of reading relation R1 C(R2) - ...
0
votes
1answer
27 views

Fetch correct stats from database

I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have ...
1
vote
1answer
39 views

Improve query performance of subquery with MAX()

I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server. We have a ticket ...
2
votes
2answers
86 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
1
vote
3answers
87 views

View: Ignore a left join if it is not used?

I have a web service (GIS feature service) that references a view called gis_sidewalks_vw. create table gis_sidewalks ( id number(10,0), last_edited_date date ); insert into gis_sidewalks ...
0
votes
1answer
11 views

how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

The query I am using to retrieve data is as "select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb," + " sum(totalqt) as tqdb," + " sum(payed) as tpdb," + " sum(...
1
vote
1answer
41 views

Speed up complex join with disjunctive condition

I have a table that contains information about orders made by users and their phone numbers. The table looks like this one: table Orders ( OrderID int, UserName varchar(50), PhoneNumber1 ...
0
votes
1answer
96 views

SELECT row based on range from columns in second table

I want to SELECT all rows from first_table INNER JOIN second_table for values where the column in first_table fall within range of values from two columns in second_table. As example, I have created ...
5
votes
3answers
158 views

Manipulating a Column Containing Key/Value Pairs

I'm accessing and creating reports from a vendor via a replicated SQL Server database. They've done some absolutely insane things that I've been trying to solve for, but this one takes the cake. ...
0
votes
0answers
12 views

LEFT JOIN on AFTER INSERT Trigger in MariaDB or MySQL?

Is there any simple way to access records related to a new one that is causing a trigger? I am allocating expenses for vehicle use, by creating a general journal entry via AFTER INSERT Trigger when a ...
0
votes
1answer
27 views

Conditional Join in DB2

2 tables are given Table T_PRICE P_Price P_Type P_Route 15 red long 10 long 5 small Table T_ROUTE R_Type R_Route blue long red ...
1
vote
1answer
45 views

How to use parent table column inside subquery JOIN condition?

I am writing a SQL Query for pulling records from multiple tables for sending the update or added new values email notifications to the user. In below SQL Query I am getting all records which are ...
0
votes
2answers
26 views

I am not able to get the result when joining two tables with where clause with OR condition

I have two tables projects and project_user_cross. I want to get the projects if the user id exists in either of the two tables Here is the projects table Id | project_manager_id | name | ...
0
votes
2answers
29 views

Group results from two different tables on same hours

I have two simple tables: indoor id | timestamp | temp | humi outdoor id | timestamp | temp And two selects which give me time and average temperature grouped by same hour for the last 24 hours: ...
0
votes
0answers
15 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
0
votes
0answers
39 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
0
votes
1answer
36 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
1
vote
2answers
37 views

Select the students that are enrolled in both the courses [closed]

This is the table definition. I want to select the students that are enrolled in courses (1 and 3). From this dataset the expected output is 'B'.
2
votes
1answer
53 views

Improving MySQL query performance for a large database

have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values ...
0
votes
0answers
25 views

Show last line with left outer join

I have a problem with my syntax, I need to show the last record using inner joins, I used this example (https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-...
0
votes
1answer
24 views

Joining tables with all rows for each unique column value

I have 2 tables as follows: | A | +----------+ | ty_date | | ly_date | +----------+ and | B | +-------------+ | locationid | | ty_date | ...
0
votes
0answers
40 views

Is there a more efficient way to join a big table that this?

Consider the following. On an e-commerce site I have a sales_order table and a subscription table. The sales_order table has well over 1 million rows and about 25+ columns. The columns I'm ...
3
votes
2answers
119 views

LEFT JOIN confusion

I am having issues with this simple scenario. I'm probably missing some basic concept... I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle....
0
votes
1answer
25 views

REPLACE(orig,match,replacement) multiple matches in one query

I'm trying to categorize Magic The Gathering cards. Here is a simplified version of what I'm doing/trying. Is it reasonable/doable to get my desired output from mysql? If so, how? Or am I going to ...
-1
votes
1answer
63 views

How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?

My situation (greatly simplified) is as follows: Table1 ID Name Ver1 Ver2 Ver3 Ver4 Table2 ID Name ... This table holds the versions The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
0answers
19 views

Order of operations in LATERAL join?

Consider the following query to find the (spatial) nearest neighbor; the PostGIS specific components here should not be of importance for my question, or so I believe: SELECT a.id, c.geom FROM ...
1
vote
1answer
38 views

How to increase the value of a column in a table by a constant by consulting another table?

I have prepared the database of the college given as create table depts( deptcode char(3) primary key, deptname char(70) not null); create table students( rollno number(2) primary key, name char(50),...
0
votes
1answer
25 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
2
votes
3answers
64 views

How to use left join with a subquery

This or this questions do not answer my doubt. I have the following set of tables in a database called order_mgmt_sys (Please note that database is static and I can't alter the tables) mysql> ...
0
votes
1answer
17 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
0
votes
1answer
40 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
25 views

How to insert values to one table from the table stored in another database in postgresql?

Here is the structure of table: `* Table 1 of database A contains mobile number and userId and both field cannot be blank I want to fetch data from table 1 of database A and insert that with some ...
0
votes
2answers
36 views

Joining 2 tables based on Dynamic criteria

Hoping you can help me. I have 2 Tables (Table PS & Table A). What i am trying is the following ; Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following ...
0
votes
1answer
32 views

How to Join two tables with the join being regexp_replace [closed]

Need your help, I have two tables “temp” and “temp 1” The temp table I am deriving a column called numbers. I am wondering how do you join table temp1 onto temp and joint on SELECT regexp_replace(...
0
votes
0answers
29 views

MySQL Count rows of multiple tables

CREATE OR REPLACE VIEW organizedevent AS SELECT o.organizerID AS "Organizer ID", o.organizerName AS "Name", o.contact AS "Contact", COUNT(e.eventID) AS "...
2
votes
1answer
42 views

Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?

Does the ANSI Standard find the following SQL sinppet syntactically correct? I am interested specifically in the last line: SELECT name, dept_name FROM employee JOIN department ON employee....
0
votes
0answers
20 views

MySQL query that works but extremely slow

I have a developer who has created a query joining 3 tables in a method that I have never seen before. This query is working although extremely slow. I am trying to understand how this query is ...
1
vote
2answers
46 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
0
votes
2answers
60 views

How to display the dates without DUPLICATE and ORDER by MAX DATE

I just want to ask that how can I display the null values along with the MAX date. SELECT MEMB.MEMB_N, MEMB.LAST_M, PrintDate FROM MEMB INNER JOIN tblPrint ON MEMB.MEMB_N = tblPrint....