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