Questions tagged [join]
An SQL join clause combines records from two or more tables or views.
-1
votes
0answers
16 views
How to prevent showing of repeated data when using INNER JOIN [on hold]
I am trying to retrieve salesID and purchaseID from two tables using INNER join on the prodID which is 9.
I used this query:
SELECT `transactions_sales-items`.`salesID` as salesID
, `...
0
votes
2answers
13 views
Inner join or cross join Confusion
For example select * from a inner join b on a.i=b.i ; is definitely inner join.
But can you let me know whether the below statements belongs to cross join or inner join ?
select *
from a ,b
...
0
votes
1answer
15 views
Count Total Transaction each specific row even if 0
I have a 3 Columns namely:
Payment Channel
Payment Name
Total Transaction
The Total Transaction will be the COUNT of each Payment Channel and Payment Name.
In the Payment Channel, values have PC01-...
1
vote
1answer
25 views
SQL Outer Join while conditioning on an extra column
I am trying to pull up an outer join between two tables, on a specifc column "Identifier", however, I would only like to carry out the outer joins on the rows where additional column "Date" matches ...
1
vote
1answer
40 views
How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient
I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
-1
votes
1answer
39 views
Query joining four tables resulting in one row
I have these two queries which pull data from four tables:
select files.id, files.name, dirs.dir
from files
inner join dirs on dirs.id = files.idDir
where files.id = 45
select label
from labels
...
0
votes
1answer
30 views
Oracle DB union two select statements which return multiple rows
I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
0
votes
0answers
30 views
Comparing count of 2 tables, is this the correct way?
I'm building a survey system where
A client that has many users
A user can have multiple surveys.
I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
0
votes
2answers
26 views
How to SELECT most common (viral) from a table and LIMIT results for pagination?
Sorry if it's a dubplicate but I can't find an answer to the question.
Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is ...
0
votes
1answer
56 views
Full Outer Join not working as expected [closed]
I want to make a view from these tables. The first inner join works as expected and brings a new column with AuspraegungID from which I want to connect to the ArtikelVarianten table
At the moment I ...
0
votes
2answers
48 views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
1
vote
2answers
62 views
Exclusionary Left Joins
I'm trying to join 2 different tables (Table99 and Table2) to Table1. The first is a straight-forward RIGHT JOIN. The second is a LEFT JOIN, where I'm trying to take Table1 and Left Join to a Table2,...
1
vote
0answers
32 views
Using conditions to return specific rows
From the following table i am trying to retrieve specific rows based on a criteria
+----+--------------+------+
| ID | UPDATED_DATE | TYPE |
+----+--------------+------+
| 1 | 01/13 | P |
|...
0
votes
2answers
26 views
Mysql query to fetch records which are not matched form both tables
I am new to here.
I have two two tables , one is temporary table and another one is main table.Before doing final process user click on refresh button . Then I am populating data into the temporary ...
0
votes
1answer
45 views
Join 3 tables with where and sorting clauses
I have 3 tables, vendors, vendor_modelsand cameras. I am trying to get some values from db all of vendor_models but name from vendor too.
Each camera has a reference of vendor_model as model_id.
10 ...
0
votes
1answer
21 views
Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code?
I'm not sure if I can ask this question here. Let me know if I can't. Basically, my code works fine, but I would like to improve the code itself.
I'd like to improve this code to require fewer lines (...
-4
votes
1answer
45 views
Tsql joins on sql server [closed]
What type of joins would you use if you were trying to find missing data in two related tables? Would it be an inner join or an outer join?
1
vote
1answer
35 views
Handle 4 tables and to show 4 columns as result
i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns:
I want to show this 4 rows, each from a different table.
EDIT: Each company belongs to a Sector. ...
1
vote
2answers
26 views
Comparing duplicates and choosing the duplicate with most recent date in a join
Assume i have table A:
id: name: timestamp:
1 Bob 2018-05-31 04:46:37
2 Alice 2019-01-31 07:13:56
1 Bob 2019-01-15 18:23:74
And i want to join with another table B:
...
0
votes
2answers
28 views
MariaDB - breaking up a multi join into CTEs
So I have the following query:
SELECT
i.mongodb_id
FROM
display_list_lists dll
JOIN
display_lists dl
ON
dll.display_list_id = dl._id
JOIN
list_items li
ON
dll....
0
votes
1answer
25 views
sql - select slow of two table
i have two table in my mariadb database, A and B :
my request is very slow 3 min 18.028 sec min with index.
sql request :
select Cid,time,step,cpu
from A,B
where B.CID2=A.CID
group by CID;
...
0
votes
2answers
47 views
How to speed up query with anti-joins
I have a query with 2 anti-joins (UserEmails = 1M+ rows and Subscriptions = <100k rows), 2 conditions, and a sort. I've created an index for the 2 conditions + sort, which sped up the query by 50%. ...
0
votes
1answer
16 views
Join query with IN condition and Group_concat
I have an issue to get concatenated text from below two tables(product_to_category & category_description)
I need mysql query to get output as "Electronics,Electronics Component Connectors,...
0
votes
2answers
26 views
Join queries are not working as expected when trying to compare a count result with a value
I'm learning SQL from a book and I'm trying to do some exercices on join queries. The only problem that I'm facing is that all of my join queries are not working while they seem well.
These are the ...
2
votes
0answers
58 views
Postgres / slow query / order by
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see issue is in ORDER BY statement. But increasing ...
1
vote
1answer
48 views
perform multiple conditional join on multiple tables
I have a situation where I need to get result based on conditional joins
For example,
SELECT
CGI.SALES_BRAND_BSNSS_ID,
CGI.SALES_BRAND_CD,
NVL(MTRX. matrix_score_cd,'RV1') ...
7
votes
0answers
57 views
What cases benefit from the Reduce, Replicate, and Redistribute join hints?
The From Clause Documentation starting with SQL Server 2008 briefly mention 3 join hints and their basic mechanisms:
Reduce
Replicate
Redistribute
However there does not seem to be much information ...
0
votes
1answer
27 views
Compare sum of columns between tables
I'm trying to create a query for this problem.
Suppose I have these two tables:
Acceptor table:
ID TYPE QTY
-- ---------- ------------------
1 AB+ 1
2 O- 5
3 O+ ...
-2
votes
1answer
34 views
Postgresql - LEFT JOIN error
Apologies ahead of time for the novice question as I am working through building my first database. I've come to joining my tables (I have multiple), but have decided to test one first. I am trying to ...
1
vote
1answer
24 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
86 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
32 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 ...
0
votes
0answers
33 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
47 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 ...
1
vote
1answer
242 views
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
38 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
16 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
52 views
Can a transitive join loop infinitely?
I have a table that stores alternative product:
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------...
0
votes
1answer
41 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
70 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
41 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
39 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
41 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
52 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
52 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
43 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
34 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
28 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
12 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
--------------- ------------ -----------------
...
