Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,473
questions
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 ...
