Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [join]

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

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 ...
0
votes
0answers
11 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
37 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
32 views

SQL join multiple “id” columns from a single row to another table [on hold]

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
0answers
4 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
60 views

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

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
30 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
26 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
39 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
38 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
51 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
57 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
109 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
45 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
18 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
16 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
17 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
64 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
11 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
12 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
26 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
17 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
43 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
54 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
282 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
58 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
18 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
39 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
33 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
27 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
36 views

MySQL Join with empty table

I have these tables select * from mass_categories; +----+--------------------------+ | id | categorie | +----+--------------------------+ | 1 | ESI-QTOF | | 2 | MALDI-...
0
votes
0answers
26 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
76 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
31 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
47 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
21 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
38 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
46 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
26 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
49 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
29 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
16 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
23 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
11 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 ...
0
votes
0answers
39 views

SQL Server group by date, time between status

I have a table that looks like the following: id_usr dt_event Sequence 0000001 2015-08-01 14:57:21.737 1 0000001 2015-08-06 15:34:21.920 2 0000001 ...
0
votes
0answers
45 views

postgres: join tables, order by GREATEST

Having the following table structure table1 id: BIGSERIAL updated_at: TIMESTAMP WITHOUT TIME ZONE DEFAULT now() ... CREATE INDEX table1_updated_at ON table2(updated_at); table2 table1_id: ...
0
votes
2answers
28 views

Cannot figure out how to remove this Using Temporary

I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good ...
0
votes
1answer
30 views

specify the correct element to pick in JOIN mysql

i Have three tables: --------------------- | PRODUCTS | --------------------- | id | product | --------------------- | 1 | super prod 1 | | 2 | super prod 2 | | 3 | super prod 3 | |...
0
votes
0answers
12 views

How to find full siblings? I have been able to find the parents, but can't figure out how to join siblings based on having same parents

I have two tables (table/column names not my choice): | Person | | Parent | ------------------------------ ----------------------- | ...