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.

-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 --------------- ------------ ----------------- ...