Questions tagged [join]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
108 views

Left outer join to itself to find outliers?

I have a table of item types and the coordinates of the items, like this: ItemType | X -------------- 1 | 1 1 | 5 2 | 5 1 | 6 2 | 6 ...
0
votes
1answer
13 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 ...
1
vote
1answer
25 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
0answers
26 views

Left join where IS NULL gives not expected results

I have two MySQL tables where table1 is supposed to contain the exact numbers of rows of the second one and table1.id is supposed to be equal to table2.t1_id. However both tables have different sizes, ...
0
votes
0answers
17 views

Mysql considers a constant column NULL in left join

I have a query that uses an left join between a table and a subquery. The subquery has a constant field 'Toys' as Product that in the final result set returns null for every row. The query is: ...
1
vote
2answers
120 views

MYSQL Update data based on top 10 records of each user

I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
0
votes
3answers
52 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> ...
36
votes
7answers
64k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
0
votes
2answers
98 views

WHERE on SQL with JOIN on large tables

We have a set of large tables (millions of records) on a MySQL DB, having schema like so (simplified): T1: id, uid, rid, text1, text2, int1, int2, ... T2: id, rid, tag_id, created_at T3: id, owner_id,...
0
votes
1answer
16 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 ...
1
vote
1answer
2k views

Index is not used when joining two tables in a stored procedure

In MySQL 5.7, I have two tables: CREATE TABLE `periodholdings ` ( `stkcode` varchar(255) NOT NULL, `participantid` varchar(255) DEFAULT NULL, `participantname` varchar(255) DEFAULT NULL, `...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
0
votes
1answer
32 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
0answers
7 views

link two tables with sqlalchemy [migrated]

I have built two tables in my postgres CLI (test2_table: id, name, age, profession, city, country). The second is referenced to my 1st table's id and has a column called wage. When I run the syntax ...
1
vote
1answer
1k views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0
votes
1answer
24 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
30 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
4answers
142 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...
0
votes
1answer
138 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
0answers
28 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 "...
4
votes
3answers
3k views

Join Multiple Tables for Aggregates

How do I join multiple tables for aggregates with Sum and Count functions? The query I'm trying is as below: Select campaigns.id, campaigns.name, Count(landers.campaign_id) As landers_count, ...
0
votes
1answer
101 views

How to get only one acl_groups_to_resources.bitmask for a given set of parameters?

I have the following tables in my DB: CREATE TABLE `restricted_components` ( `component_id` int(11) NOT NULL, `resources_id` int(11) NOT NULL, `component_dom_id` varchar(50) NOT NULL, `...
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
2answers
45 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....
1
vote
2answers
45 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
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 ...
0
votes
0answers
30 views

Two left joins then two on statements [duplicate]

I stumbled upon a left join function that is throwing me off... I was trying to eliminate a subquery or using another temp table. Both of the following queries produce the exact same values. SELECT * ...
0
votes
0answers
24 views

How to get the data using where_in (1,3 ) table in mysql?

We have table Table 1 is User Table 1: id | name | sub_code ----------------------------- 1 | name1 | 1 2 | name2 | 1,3 3 | name3 | 2,4 Table 2 is ...
-1
votes
1answer
29 views

Help with joining two tables (full join)

I have two tables. Table 1 : 'DQ' is the golden source of information Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare AMOUNT from the 'DQ' table vs VALIDATED in the '...
0
votes
1answer
26 views

Using WHERE / AND in Joins

Can someone elaborate on how the following two queries would provide different results? That is, how the query is processed differently using WHERE or AND. select orders.*, accounts.* from ...
2
votes
1answer
103 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
-4
votes
2answers
49 views

Joining two tables together based on common identifier [closed]

I have two tables. Table 1 : 'DQ' is the golden source of information, Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare the DQ table to whats inthe PS Table and ...
0
votes
2answers
98 views

INNER JOIN is extremly slow on some rows and create temp table

The MySQL query SELECT `p`.*, `p.Author`.* FROM `post` AS `p` INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid` WHERE ( ( `p`.`threadid` = 29780 ) AND ( `...
0
votes
1answer
22 views

How to aggregate results from SELECT JOIN

I have two tables: **user Table ** id username --- ----------- 100 greg 101 john 102 patrick 103 bill **user_subscription_plan Table ** id user_id amount --- --------- ------- 1 ...
0
votes
2answers
120 views

changing from inner joins to left joins to include null values?

I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date ...
0
votes
3answers
119 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
0
votes
0answers
31 views

how to reduce time of a query which have 4 joins

I am new to CakePHP and I am working on CakePHP 3.8 version. There is a join query which takes almost 10 -12 seconds to fetch data. I don't know how to optimize this query. this query is written in ...
0
votes
1answer
162 views

mysql - find duplicate sets of data grouped by foreign key

I have a large mysql table (500,000 records). I need to find sets of data that have all the same attributes (KEY, NAME AND VALUE). Some sets have 20 attributes others have 397 per each KEY. The table ...
0
votes
1answer
97 views

Natural join question, I have the answer, but I don't know why it's correct. Help?

So I have a relatively simple database schema. I'm not sure why the where clause in the answer magically makes the dept_name duplication problem go away. I'm new to all this and I'm sure the answer is ...
0
votes
2answers
111 views

Mysql join not working

I have database with two tables: logs & src_info mysql> describe logs; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
0
votes
0answers
19 views

Adding rows that share a foreign key ID from the same table *in a new row*

My goal is as follows (simplified): find the newest record from table events use a FK events_meta_id to find all records from that same table (events) that have the same value in that field, if they ...
0
votes
2answers
33 views

SQL Server: Join all tables from a table listing their names [duplicate]

I have a table that contains a list of metrics and their IDs in the format: L6Metrics | ID | Name | -------------------------- | 734 | L6A_Velocity | | 736 | L6B_Velocity | | 738 ...
0
votes
3answers
116 views

MySQL slow performance and timed out after executing join

I'm not a MySql Server DBA. Two years ago I've created web service that send messages and inserts responses from remote web server in MySQL database (Server version: 5.6.16 - MySQL Community Server). ...
0
votes
1answer
17 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
7
votes
2answers
297 views

Nested-Loop-Join: How many comparisons and how many pages-accesses?

Say you have two relations R and S , where R has 1000 tuples and 100 page-accesses, and S has 50 tuples and 25 page-accesses. Assuming R is the outer relation, then how many tuple-comparisons and ...
0
votes
1answer
36 views

Problem with How to join with subquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
1
vote
2answers
149 views

Full Outer Joins Slowing Down Query

I am relatively new to SQL, so I apologize if I ask some basic questions. Our database is setup on Azure, we have automatic indexes turned on. The query I have takes about 6 seconds and gets 2217 ...
0
votes
1answer
177 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
1answer
47 views

How to left join on groups of data

I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 ...