Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
1,575
questions
0
votes
1answer
48 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
186 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
41 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
16 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
30 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
15 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 ...
0
votes
0answers
19 views
Postgres count the same join by different values
I've been trying to create a query that counts the number of different types of events associated with each entry in another table
city job job_events
id | name | state ...
0
votes
2answers
123 views
Get single records when duplicate records exist in 2 table
I have 2 tables. TableA is the sales and TableB is the leads table. I'm trying to see if the people in the leads TableB made a purchase and their name appears
on the sales TableA. If they do I only ...
1
vote
3answers
84 views
SQL join multiple “id” columns from a single row to another table [closed]
Apologies if this has been asked but I've tried searching and haven't come across anything similar. I have a two tables in a MySQL database. Athletes and Teams
The athletes table contains the ...
0
votes
1answer
92 views
Complex queries in CouchDB across multiple documents
I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something.
Assume the following (simplified) document examples:
{ "docType"...
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 ...
2
votes
0answers
33 views
Need to update a table with an iterative number based on a subquery
Massive edit to follow procedure and enable reproducing this in a fiddler somewhere.
Table 1, schedule:
CREATE TABLE `schedule` (
`scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`...
0
votes
1answer
200 views
What is the correct way to select rows from either side of a bridge table?
I want to do an SQL query for three columns. The database has five tables, two of which are bridge tables for the other three. When I attempt to select from the tables, I get a very large number of ...
0
votes
3answers
169 views
Usage of aggregate function with when self-joining a table
I want to find people from a database who has a weight bigger then the average weight:
SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)
But I have an error:
Result: misuse of ...
0
votes
2answers
76 views
SQL query to list all student users, add column to show who were logged in at least once
I have a query, which lists those users, which are students:
SELECT *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid
WHERE
ra.roleid = 5 /* is student */
I have a table ...
2
votes
1answer
61 views
How can i select columns from two tables using REF
I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects ...
2
votes
0answers
83 views
MariaDB behaviour with many JOINs [closed]
I found MariaDB behaviour changing according to the number of JOINs in a query.
In this particular application, when the number of JOINs goes from 30 to 31 the execution plan changes, causing ...
3
votes
1answer
420 views
JOIN to obtain all the rows from the first table along with NULLs from the second table
I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 ...
0
votes
1answer
47 views
Sum between multiple joined tables
I have these tables:
DROP TABLE IF EXISTS books;
CREATE TABLE `books` (
`bookId` mediumint(8) UNSIGNED NOT NULL,
`title` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `...
0
votes
0answers
34 views
Help structuring a query with multiple many-to-many relationships
Assume that there are 4 tables, with association tables "between" each of them:
Machines
Products
Option Groups
Options
Machines may serve many products, and a product may "belong to" many machines. ...
1
vote
1answer
41 views
Get the combined row count by month from 3 tables
Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below:
CREATE TABLE ...
0
votes
1answer
20 views
Return results of two MySQL query in only one
I am running two seperate queries to get statistics from 3 different tables:
contacts
campaigns
sent_messages
The first query counts all contacts that are marked as "stop" in the contacts table (...
0
votes
1answer
87 views
Cannot get a user function to co-operate with a subquery in mariadb
The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.
Notes:
Problem ...
0
votes
1answer
16 views
How to join two tables in postgresql
First table
Complaint_id Status Reopen
127493 5 1
127494 5 0
Second table
Parent_id Currentstatus Reopen datetime usertype
127493 0 0 2019-04-08 ...
0
votes
1answer
26 views
Join on unnested integer array column and count occurrences in array
I am trying to join two tables after I have unnested one of them. The tables are:
CREATE TABLE teachers (id SERIAL, first_name VARCHAR(60), last_name VARCHAR(60));
CREATE TABLE training_modules (id ...
0
votes
0answers
31 views
SQL Server - CASE for multiple tables
hope I can explain myself right in this question.
I have three (3) tables from which I have to build a report, this is the data selection process I need to do in order to correctly output the data to ...
0
votes
1answer
80 views
JOIN in 4 tables gets phpmyadmin unresponsive
I can´t manage to create a valid query that shows me 5 columns from these 4 tables:
As you can see the relationships that i'm trying to JOIN in the query are highlighted in differents colors.
The ...
0
votes
1answer
278 views
Will too many tables slow Mysql down?
I'm making a database which should be able to handle over 1,000,000,000 rows.
in order to optimize it's performance i've decided to split tables.
so instead of having 1,000,000,000 entries in one ...
1
vote
1answer
76 views
Using Window Function instead of Select sub-query for counting
I have to count the number of Orders a table, per Employee, for two criteria -
"Late Orders".
"Total Orders"
The query includes Orders table and Orders table. They are joined together via the ...
4
votes
1answer
346 views
Forcing execution plan with local join hints
I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between ...
-1
votes
1answer
60 views
MySQL JOIN statement
I have 2 tables like below:
product_name
id | productName
----+-----------------
1 | item 1
2 | item 2
3 | item 3
exchange_history
id | exchangeFrom | exchangeTo
----+--------------+-------...
0
votes
1answer
1k views
How to join three table using inner join in postgres sql
I am trying to get the data from the three table using inner join but its sows me empty result.
select comp.gps_time as complaint_register_date,comp.accept_date as complaint_accept_date,comp.name as ...
-1
votes
1answer
85 views
MySQL: Delete statement from complex select subquery
I would like to delete the output of the below SELECT command but I am not a DBA and my SQL knowledge is rather elementary. I want to delete the users matching the SELECT statement.
SELECT count()
...
2
votes
1answer
58 views
join table and count them SQL based on segment
I have 2 tables and I'm trying to join and count how many people have answer
each questions. I'm trying to find a better way to have only two column. the
first one will be of people who answer "Just ...
0
votes
1answer
131 views
INSERT INTO from 2 unrelated tables
I need to INSERT INTO a new table (FinalTable) from 2 unrelated tables, but values from Table1 will need to be repeat for every value pulled from Table2.
Example:
Table1 Table2 ...
1
vote
1answer
107 views
MySQL Join with empty table
I have these tables
select * from mass_categories;
+----+--------------------------+
| id | categorie |
+----+--------------------------+
| 1 | ESI-QTOF |
| 2 | MALDI-...
0
votes
0answers
173 views
PostgreSQL: join by dynamic nested JSONB attribute
I'm having quite an unusual task and I'm not even sure if it's solvable.
I have a table with JSONB column which can contain arbitrary nested JSON objects. Let's call it objects table.
And I have a ...
0
votes
2answers
87 views
How to group by week even when the count is 0
My below example works fine, the only challenge i am facing is that weeks with 0 results do not show. Here is a sample of my code:
My current output:
week | amount
4 | 354
6 | ...
0
votes
1answer
39 views
Trying to return a complete column from one table, and a count from another table
I currently have two tables that I am trying to pull data from, one being Customers and one being Bills.
Customers is a static table that just lists all the customers, but the reason I need this ...
0
votes
0answers
367 views
How to pass array of UUIDs *OR* empty array and return implicit (inner join)?
How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty?
This question (Casting an array of texts to an array of ...
1
vote
0answers
27 views
PostgreSQL - SELECT query with ORDER BY and LIMIT 4 very slow, same query much faster with LIMIT >= 40
I've got following sample query from our Postgres DB:
SELECT * FROM advertised_car a0_
LEFT JOIN car_check c1_ ON a0_.car_check_id = c1_.id
WHERE c1_.status = 'ACCEPTED'
ORDER BY a0_.id
LIMIT 4;
...
0
votes
1answer
57 views
Joins on multiple tables
Given this schema, is it possible to produce an output wherein it shows all the User's assigned modules with its matching enrollment id if any, and NULL if none?
Users
+----+------+----------+
| id | ...
1
vote
1answer
861 views
LEFT JOIN and handling NULL Values [closed]
I have two tables.
Table 1:
A B
------
1 'A'
2 'B'
3 'C'
Table 2:
A C
----------
2 15
3 20
I'm using the following query:
SELECT table1.A, table1.B, table2.C
FROM table1
...
0
votes
1answer
98 views
How to JOIN with missing rows?
I need to JOIN two tables to correct the price with exchange rates as
SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date
The problem is that table2 does not have the ...
1
vote
0answers
32 views
Is there a way to let MySQL join some external data without importing those data into DB?
I have some static data which stored in a JSON file:
outinfo=
[{out_id:123,out_name:'hello'}]
and I have a table:
----tableA----
| id | label | out_id |
| 1 | a | 123 |
| 2 | b | ...
3
votes
2answers
51 views
Query is faster with additional JOIN
SELECT COUNT(DISTINCT User_id) FROM Session
GROUP BY Floor_id;
SELECT COUNT(DISTINCT User_id) FROM Session
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;
The second query runs 20x faster ...
0
votes
1answer
381 views
MySQL - LEFT JOIN with ORDER BY is slow
I have three tables in MySQL - gallery, gallery_likes and gallery_info.
I want to obtain photos from gallery based on the folowing query:
SELECT g.photo_id, g.name
FROM gallery g
LEFT JOIN ...
-2
votes
1answer
36 views
How to retrieve a users uploaded files using foreign key
I have two tables,users, and,files,
Users
Id,firstname,lastname,email,password
files
Id,filename,description,userid
Userid in files is foreign key witch relates to Id in users
I am trying to ...
0
votes
1answer
25 views
mysql query with 1 derived table and 1 inner join takes forever to load
I tried removing some chunks of code little by little and found out that the condition inside the inner join is the devil. Can someone enlighten me on how to achieve this when I have like 10,000+ rows ...
0
votes
1answer
15 views
Help with optimizing Oracle Query - nested with count
I have 2 tables (Students and Cellphone).
Columns in Students: StudentID, FNAME, PHONEID
Columns in Cellphone: PhoneID, Phonename
I want to create this:
• Run a query to show the most popular ...
