Stack Exchange Network

Stack Exchange network consists of 174 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
1answer
12 views

LEFT JOIN based on content of a field in matched rows

Generically, I'm attempting to take a table and join it with another table but only on rows that have a specific value in one of the fields. Specifically, I have a users table and a contact table. ...
2
votes
1answer
72 views

Refactoring subquery to JOIN and CROSS APPLY, get only row for each record in parent table

Given the following query: SELECT p.ProductName, CASE WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1) THEN 1 ELSE 0 END ...
0
votes
1answer
18 views

Select from one table where range is held in columns belonging to another table

I am trying to write a SELECT statement in Oracle SQL where I have a TABLE A with a "START" column and an "END" column which holds division numbers. I want TABLE B to only extract those division ...
-2
votes
0answers
30 views

How Sql query for tree from two tables [on hold]

table : Group id parentId name ---------------- 1 0 Category1 2 1 Category1_2 table : Depart_Group id parentId name Group_Id ---------------- 1 0 Name1 1 2 1 Name2 ...
0
votes
0answers
29 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
1answer
40 views

How to optimize UPDATE with a nested SELECT subquery?

I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do: In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1answer
24 views

Trouble updating column with matching data from another table (3 diff. attempts: 1 w/out errors but no results, 2 with errors I don't understand) [closed]

Table 1 is 'z_scrapestorm_rentals' and table 2 is 'pcpao_cols'. They both have address columns named 'address_1_line_trunc' and 'address_one_line' respectively. Table z_scrapestorm_rentals: column ...
0
votes
1answer
189 views
+250

What is a “PARTITIONED OUTER JOIN”?

This just came up in a question on Reddit, and I'm wondering What is a PARTITIONED OUTER JOIN in Oracle? (definition) What does a simple example look like? (use) How would you write it in terms of ...
0
votes
1answer
33 views

Natural join output doesn't appear in table [closed]

I used this query: select * into airbnb_parcels from pcpao_res natural join AIRBNB_PARCELS; and got: Error: relation "airbnb_parcels" already exits. In DBeaver I used the following query: ...
0
votes
3answers
15 views

MYSQL: Display NULL value when foreing key is NULL

I have the following database structure in MYSQL: CREATE TABLE xs_bugtracking ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `product` VARCHAR(64) NOT ...
0
votes
2answers
50 views

Can a transitive join loop infinitely?

I have a table that stores alternative product: +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------...
0
votes
1answer
33 views

Using MATCH, AGAINST multiple times in MySQL fulltext search

I'm using MySQL database for my e-commerce project. And here is my query for search: SELECT DISTINCT p.id, p.name, p.price, p.created_at, MATCH(p.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN ...
1
vote
1answer
64 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
0
votes
1answer
24 views

SQL query - Do I need a full join to produce this combined result?

With two tables structured like this: Table1 Table2 ---------------- ---------------- id, time, x id, time, y, z 1 1 1 1 2 ...
0
votes
2answers
38 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
2answers
36 views

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice? CREATE TEMPORARY TABLE folders (...
1
vote
1answer
34 views

Performance of mysql equi-join observed in HDD and SSD

I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries. Mysql version : 5.7.18 Engine : Innodb Cache state : Mysql restarted before experiment ...
1
vote
2answers
43 views

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
0
votes
2answers
50 views

WHERE clause on a TIMESTAMP column causes serious performance penalty

I have a pages and a page_views tables with 100k~ & 4M~ records respectively. I run a query to get the top pages that have the most page_views: select `pages`.`id`, `pages`.`host`, `pages`.`...
3
votes
0answers
42 views

Query for getting reserved seats in a cinema

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular ...
0
votes
0answers
30 views

Mysql How to get joined tables data horizontally

I'm trying to get the addresses with that compairing amounts horizontally which found twice in address table. I'm getting the address name and product name but couldn't figure out how to get amounts ...
0
votes
1answer
26 views

Slow query with multiple group by

I have a query to get all the comments from threads where a given user has commented and not yet seen the comment. So if user 1 commented to a topic, then user 2 commented after his comment, then user ...
0
votes
1answer
10 views

get all posts with their associative tags without duplicates

Two tables. Post and Tag. Many to Many bi-directional relationship. Post Table Tag Table Post_Tag Pivot Table --------------- ------------ ----------------- ...
0
votes
1answer
25 views

I have 2 queries with only one difference that I want to merge in just one query

I have two queries in MySQL that are basically the same, where the only difference between them is a min() or max(), I would like to know if it is feasible to join the two. I believe that the ...
2
votes
1answer
34 views

Select the same field based on different foreign keys

I have two tables: Containing a list of products with ids and names A table linking several products with parent-child relationship based on ids using foreign keys. I want to create a view that will ...
1
vote
4answers
185 views

SQL Join to a Same Combination of Tables

I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join to same set of tables. Table A may have 8 columns of different data ...
0
votes
1answer
28 views

For each implementation in joins

deliveries Table has customer_id and the item_id they want to buy. items table has items with item_id as primary as below deliveries items id customer_id item_id ...
0
votes
1answer
21 views

SQL query on SUM function plus JOINS

Sorry if this has been covered before but I couldnt find an example to my issue Situation: Say I have a table of sales and want to display them Step 1) SELECT payment_id, amount, payment_date FROM ...
0
votes
1answer
14 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
0
votes
1answer
22 views

How to generate missing values for each employee for each day

CREATE TABLE `EmpWorkCount` ( `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT, `day` int(10) unsigned NOT NUL, `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `...
1
vote
1answer
24 views

MYSQL Select data from two different tables BUT represent the data in multiple rows

I am needing to select all email addresses from two different tables with a single query. The goal of this is to list out all email addresses a client has per row. Both tables are structured ...
0
votes
2answers
48 views

How to efficiently handle querying for different combinations of optional parameters

Consider the following Tables: Table: Parts part_id INT PK part_number VARCHAR Table: Jobs job_id INT PK job_number VARCHAR Table: Job_Parts part_id INT FK PK job_id INT FK ...
1
vote
1answer
25 views

Join against two tables to get single combined result set

tl;dr I made a SQLFiddle here http://sqlfiddle.com/#!9/031e71/1 with a minimal, complete, reproducible test case (though very specific to the problem domain of orders and products). I have a Zen Cart ...
-3
votes
1answer
28 views

Left join not working - not talking about my bones

I have two tables dbo.eletter: eletter_id eletter_title eletter_body eletter_form eletter_teacherID eletter_signed eletter_parentID dbo.status: status_id status_letterid status_parentid ...
0
votes
1answer
30 views

JOIN tables and return count even if it is 0

I have this query: select settings.customer_id, current::jsonb->'alarms' as alarms, from settings join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res....
1
vote
2answers
30 views

Remove multiple joins to same table with where

I am stucked at below situation. Need expert advice. I have 3 tables, login_details,deleted_login_details and custom_fields as below. login_details structure = deleted_login_details custom-fields have ...
0
votes
1answer
33 views

Two left joins where one exists?

Basically, I have table a, which has a type and type_id and want to join on either two different tables where there is a hit. So basically, if table b has a match, return the record or if table c ...
0
votes
1answer
23 views

Which query execution will be faster

In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying. Also, can you guys suggest some better tools ...
0
votes
1answer
32 views

Left join for unique pairs, 0 otherwise

Just to continue the question , I'm struggling to do left join and to keep equal sized time series generated in the previous step. So I have customer_date_dimention table with equal time series for ...
2
votes
2answers
150 views

How to join global temp tables in SQL Server?

I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this: SELECT * INTO ##A ...
0
votes
1answer
36 views

How to show missing rows with default values

I have a table Category which has a few missing records for a few months. The table looks like this Category Month Amount Opt January 12.00 Opt February 0.00 Opt ...
0
votes
1answer
19 views

MySQL, comparison between, tables with not all records common, on 2 time intervals

I want to make comparisons on the volume of sales, turnover and margin of all product sales between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31). for example: Product ...
0
votes
1answer
15 views

Unexpected result when joining a table in a year range

I'm working with an users, user_roles and point_standards. Users need to achieve a certain points in a certain period based on which role they have. Points are retrieved from courses which are done in ...
1
vote
2answers
294 views

Casting an array of texts to an array of UUIDs

How can I cast an array of texts into an array of UUIDs? I need to do a join between two tables: users and projects. The users table has an array field named project_ids containing the project IDs ...
0
votes
1answer
48 views

What and where to index, rapid increasing table postgres

I work as a software engineer with python and django. Currently I am struggling with a design choice made before my time. We have a transaction table that logs all customer activity. Due to the ...
0
votes
2answers
58 views

How to get last 12 months values when some months have no records in the table

I have a table which has PartNumberKey and Depot_ID as composite primary key. It returns the following data - The requirement is to add the month columns for which there is no row with 0 as Total ...
1
vote
0answers
34 views

Equi Join On Union- and Intersection-Compatible Relations

Suppose that I have two union- and intersection-compatible relations; i.e., R1(A,B) and R2(C,D) as: Now , I want to find the join of both relations with equality conditions A=C and B=D. I have ...
0
votes
1answer
25 views

problem to join several tables without relation

My simplified database scheme: CREATE TABLE `stores` ( `idStr` int(11) NOT NULL, `nameStr` varchar(255) NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `channelProvider` ( `idCP` ...
0
votes
1answer
33 views

How to join 3 tables in MySQL

I’m looking at 3 tables here with: 1 called Users containing a UserID. UserID is linked by a foreign key to UserID in a table called Orders. Within Orders there’s a PassengerID linked by a foreign ...
1
vote
0answers
27 views

PostgreSQL - Speeding up Index scan in left join

I have two tables: Order Lines, Orders. Orders table has a Status column, which is text. It can have values like 'Draft', 'Approved', 'Cancelled' etc. I need a query to return pending order lines for ...