Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
25 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: ...
0
votes
3answers
51 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
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 ...
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 ...
0
votes
1answer
29 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
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
29 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
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 "...
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
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
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....
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 ...
-4
votes
2answers
48 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
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
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
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
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
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 | ...
0
votes
1answer
35 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 ...
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
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 ...
0
votes
1answer
34 views

Database design for Scavenger Hunt Game

I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times). The first idea ...
0
votes
1answer
40 views

Why last table don't using index for joining?

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
3
votes
1answer
31 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
0
votes
1answer
35 views

How to use left join with Where clause

I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no ...
0
votes
2answers
13 views

Select value based on a test

I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address. I need to ...
0
votes
0answers
20 views

Why does MySQL tend to avoid indexes with few non-NULL values, even when it has a large penalty?

Over the last upgrades (5.6 -> 5.7 -> 8.0), I've observed that MySQL increased its aversion to indexes with few non-NULL values. This caused some seriously underperforming queries that I needed to ...
0
votes
1answer
34 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
0
votes
1answer
25 views

Why this join provide multiple value?

This is my Query: SELECT GROUP_CONCAT(c.categoryName) AS categories FROM post p INNER JOIN join_post_cat jpc USING (postId) INNER JOIN categories c USING (categoryId) WHERE p.postId ...
-1
votes
2answers
23 views

Query to collect the recent unique TimeSampled rows using inner joins for multiple servers in MS SQL [duplicate]

Dears, I have written below inner join query to get the data from my database tables. But output of this data is coming up with almost 3k lines. Output is below. I need data in only unique recent "...
1
vote
2answers
26 views

Multiple aggregates with cross (lateral) join

Following on from Combining array_agg and unnest, given this data: key | a | b | c ------------------ 1 | 0 | 1 | {1,2} 1 | 1 | 2 | {3} 1 | -1 | 3 | {2} 1 | 2 | 4 | {} Executing this ...
0
votes
3answers
29 views

SQL Compare data from two joined tables

i used SQL Server, and have two joined table Select b.team, SUM(a.total) FROM data1 A INNER JOIN team b ON a.teamID = b.teamID WHERE month = 1 and year = 2019 GROUP by team second one is like this ...
0
votes
1answer
23 views

MySQL show data from Rows to columns

I am stuck at getting a query with joining 2 tables. Below is the database structure. What I want to achieve is to list all the information from one table with join on the second table but it has 3 ...
0
votes
2answers
46 views

How to avoid conditional join in order to write a tsql view

I have a scenario where I have Roles, Set, DefaultSet, and CompanySet tables Roles table RoleName --------- Admin Viewer User Set table Id Name --- ---- 1 Employee Data 2 Help ...
0
votes
1answer
18 views

join a lookup table with a data table

I have the following schema in my database Neighbors: ( central_id, neighbor_id) Data: (central_id, value) This schema is for a convolution algorithm which for each central_id I have 6 neighbors(it ...
0
votes
2answers
26 views

How to delete data from both tables or least one of them, when using “delete from … using … where” in Postgresql?

I have this sql script for my Postgresql db: DELETE FROM table1 USING table2 WHERE table2.frg_table1_id = table1.id AND table1.id = $1 I want to it to always delete data from table1 if the ...
1
vote
1answer
41 views

How to combine two select statements result column wise?

I have two select queries and i want to combine result of both column wise.I have use union between two select queries but it give me just one query result I want expected result like this My SQL ...
-4
votes
1answer
51 views

How to get count 0 using group by if record not exist?

I have sql server query and i want to get count=0 if record not exist. I want to show all from category table , show all categories with count 0 if not exist. But its return empty if record not exist ...
0
votes
1answer
42 views

How to calculate the sums of two columns, when that two columns brought by join queries?

I ran two queries as shown bellow; query 1: queried with JobRequiredProducts table and Product table to list the jobId 1 required all product and unit prices SELECT j.jobId, j.productId, ...
3
votes
1answer
172 views

How to use JOIN and SUM function to Calculate the total of query returned column?

I ran bellowing query; Query: SELECT j.jobId, j.productId, p.productUnitPrice FROM JobRequiredProducts J JOIN Product p ON p.productId = j.productId WHERE j.jobId = 1 ...
0
votes
1answer
38 views

Write update in more efficient way with joins

How can I write this update with joins in 'more sql' way? I have been told that this is weird coding: -- Updates recipient's account UPDATE table1 SET balance = balance + @amount WHERE ...
0
votes
0answers
10 views

how to get data from multi table with column json in postgresql?

I have a lot of tables like the following tables. main_table id |customer_id | tabl2_id | tabl1_subJson_id |... ---|------------|-----------|------------------|------ 1 | 1 | 1 | ...
0
votes
1answer
26 views

How do i get a list of records from two tables that have different values based on a matching PK? [closed]

Lets say i have the following two tables: Table1: |IP |Name | Gateway | Version | +------+------+---------+---------+ |1 |A | 1.0.0 | v1 | +------+------+---------+---------+ |2 ...
0
votes
0answers
10 views

HIVE SQL - JOIN

I am trying to perform a left join between tables. The syntax looks something like this: select a.x1, a.x2, a.x3, b.y1 from a left join b on a.x1=b.x1 Ideally, the query should give all records that ...