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