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.

1
vote
0answers
13 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 ...
0
votes
0answers
21 views

How to fetch all entities whose childs all match certain condition?

I have a task table, an order_table, a big_customer table and a preferred_sender table. Here is the relationship between those tables: task 1:N order_table ## a task has N orders ...
0
votes
1answer
38 views

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 on same guid column. Table A may have 8 columns of different data ...
0
votes
1answer
27 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
20 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
12 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
22 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
44 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
24 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
29 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
29 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
32 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
22 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
133 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
29 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 ...
-1
votes
0answers
24 views

MySQL 5.7: Left join json object aggregate

I would like to capture aggregate data in a pivot table along with both parents' columns. The two parents table, (table1, table2) contains the necessary information while the pivot table contains one ...
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
14 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
80 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
47 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
54 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 ...
0
votes
0answers
10 views

get the turnover , income, total expenses from my database and group them by year-month

I have 4 tables : Products - to record products spending - to record expenses directv - to record subscription payments to TV channels sales - to record sales Products has 4 columns : idProd is ...
1
vote
0answers
30 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
24 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
31 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
22 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 ...
0
votes
1answer
41 views

Costly HASH MATCH Aggregate

I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index) Here is the view and the plan. execution plan CREATE ...
2
votes
2answers
64 views

Ignore duplicates while using join

I'm trying to ignore duplicates between two providers in my tables while using join. I'm using this query for now: SELECT * FROM A AS a1, A AS a2 WHERE a1.provider <> a2.provider AND a1.etype =...
0
votes
2answers
28 views

How to do a Join with optionally matching columns

I have two tables as follows (in a Postgres 10 database): Maize: id|acreage|date |region 1 |20 |2018-10-18|A 2 |10 |2018-10-07|A 3 |26 |2018-10-18|B 4 |06 |2018-10-07|B Paddy: ...
0
votes
0answers
18 views

Optimise empty date filling query (using a calendar table)

I don't know if it's possible but I'd like to optimise a query I wrote to get all groups of a query between two dates filled with 0 values for missing date in the interval. I'm running MySQL 5.7 I ...
0
votes
2answers
77 views

Joining 3 Tables with Left Join

I have 2 Tables namely Area_ Table and PingData_Table Area Table has the list of the areas along with the Area ID and Area Name AreaId | AreaName | isActive 1 |Jumbo House| 1 2 | DCC ...
1
vote
1answer
37 views

Finding available schedules for multiple slots

I was asked to come up with a database schema to keep track of the slots and their schedules and query the table to find out the available time(empty schedule) for the slots. I created three tables ...
0
votes
1answer
26 views

join two tables, include one row from the parent table for each primary key

I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I ...
0
votes
2answers
46 views

Insert into a table selecting data from a sub query [closed]

I would like to insert a few data into a table selecting data from a subquery having multiple joins. I tried the following way: insert into mytable (columns) select * from (select columns from ...
1
vote
1answer
26 views

SQL Server - specify build and probe tables to hash join?

I am observing a situation in AdventureWorks database where the hash join operator is building hash table using a larger input (296 rows) and then probing with a much smaller input (3 rows). I'm not ...
0
votes
1answer
26 views

Select sales from different stores and group by product

I have three tables (SalesTable, StoresTable and ProductsTable) and the structure is the following: --StoresTable StoreId Name 1 Store 1 2 Store 2 --ProductsTable Id Description 1 ...
0
votes
2answers
87 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
0
votes
1answer
50 views

Execute UNION and after LEFT JOIN in MySQL

I have 3 tables cdc_vw_Marbetes_Catalogo_Exis tblMarbetesAdicionales tblMarbetesCongeladas First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales. (SELECT ...
-3
votes
2answers
33 views

LEFT JOIN returns NULL in middle of query [closed]

Started working recently with joins again and I am trying to get the server name from a different table with matching ID's (Foreign Key). SELECT Website.`services`.`port`, Website.`services`.`...
0
votes
0answers
21 views

MySQL INNER JOIN issue

I have this query that updates my table: UPDATE mancanti_java a JOIN from_generale_java b ON a.commessa=b.commessa AND a.modello=b.modello AND a.stagione=b.stagione SET a.fabrica=...
0
votes
2answers
34 views

Speed up slow query which uses ifnull, if, sub-query, join and order in MySQL

I have one SP which generates a final query (like the one below) which is taking too much time to execute. Basically it has the following parts: IFNULL to check that if value is null then send ...
1
vote
1answer
34 views

SQL squash rows returned when joining several tables

I have a database which has a table for the main items, lets call it A with primary key 'id'. Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have ...
1
vote
1answer
30 views

Delete from table using FK columns, JOIN and LIMIT

This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
0
votes
1answer
30 views

How to UPDATE from JOIN with GROUP BY

In a SELECT query of SELECT b.id, MIN(IFNULL(a.views,0)) AS counted FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id HAVING counted>0 How can I turn this query to UPDATE as UPDATE b.number ...
1
vote
1answer
26 views

“Print” specific columns through foreign keys

I have three tables: Users Users_request Users_applications Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id ...
0
votes
0answers
31 views

Overwriting foreign key column values in JOIN statements

The foreign keys from referencing table and from original table are overwriting themselves in SQL JOIN's results. And that is happening in the order they appear in the SELECT list, later one prevails. ...
-1
votes
1answer
60 views

Is there any ways to get the row count of joined tables?

Is there any methods to get the row count of the table which is related to the specified condition.? here is my query select * from table2 t2 inner join t1 on t2.table1_id = t1._id where t1....