Questions tagged [join]

15821 questions
1

votes
2

answer
2k

Views

how to do a JOIN FETCH in jpa criteria

I am trying to translate the query below to criteria api. SELECT er from ereturn er JOIN FETCH product_item pi ON pi.ereturn_id = er.id WHERE pi.status = 'RECEIVED' To something like this: CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery criteria = builder.createQuery( Ereturn.class...
1

votes
1

answer
47

Views

Haskell Esqueleto 3 table join

These are my attempts to do a SELECT from three tables. But they don't compile and I don't understand the error (I don't know why it expects a tuple (Entity Issue, b0) instead of the triplet I think the code attempts to get). Attempt 1: {-# LANGUAGE ScopedTypeVariables #-} {-# LANGUAGE NoImplicitPre...
hhefesto
1

votes
1

answer
182

Views

How to do Oracle (+) left join query for multiple columns

We are using Oracle 11. When doing LEFT JOIN like below, the result of the query is 11,053 rows …LEFT JOIN LIQ ON LIQ.ID = SEC.ID AND LIQ.UPD_DT >= '08-JAN-19' WHERE … But, when doing LEFT JOIN using (+) like below, the result of the query is 10,136 rows ...FROM SEC, LIQ WHERE LIQ.ID (+)= SEC.I...
faujong
1

votes
2

answer
46

Views

python pandas dataframe groupby or pivot_table

Example: import pandas as pd data = {'id':[101,101,101,101,102,102,102,102], 'day':[1,2,1,2,1,2,1,2], 'year':[2011,2011,2012,2012,2011,2011,2012,2012], 'avg':[0.500,0.400,0.300,0.200,0.555,0.455,0.355,0.255], 'sum':[1, 2, 2, 3, 6, 6, 8, 9], 'div':[2, 1, 3, 2, 6, 1, 6, 3]} df = pd.DataFrame(data) df...
YilGuk Seo
1

votes
2

answer
43

Views

SQL Server: multiple select statements from the same table in one query

I'm working on two queries that pull data from one table. I can get them to run how I want individually, but I'd like to combine them into one if possible. I've tried the solution in [Multiple COUNT SELECTS from the same table in one query but wasn't able to get it figured out. Below is what I'm wor...
Jeremiah Williams
1

votes
2

answer
40

Views

Get Sum for the column based on a condition

I am working on a SQL Query. Query is something like below: Select a.field1, b.field2, c.field3, c.field4, b.filed5, a.field6, d.field7 from a Inner join b on a.field1 = b.field1 right join c on b.field2 = c.field3 left join d on d.filed3 = a.field1 where some conditions; The output of the a...
ging
1

votes
2

answer
58

Views

PIVOT with dynamic columns and JOINS 5+ tables

This is an extension of a question posted by me earlier which can be found here : Previous Question Now i have 2 more tables that are also connected to ProfileFan tracking the Activities of the Fan(ProfileFan). Table Fan ----------------------- | FanId | Name | Info | ----------------------- | 17111...
daskalot
1

votes
2

answer
90

Views

How to construct this join query with JPA API?

Two tables: CREATE TABLE `foo` ( `foo_id` bigint(20) not null auto_increment, `name` varchar(32) not null, `_deleted_` tinyint(1) default '0', PRIMARY KEY (`foo_id`) ) ; CREATE TABLE `bar` ( `bar_id` bigint(20) not null auto_increment, `foo_id` bigint(20) not null, `key` varchar(32) not null, `va...
slashdottir
1

votes
1

answer
17

Views

MySQL left join on the same table, double inverted result

I have this table: item | timestamp | value x | 1 | 30 x | 2 | 40 x | 3 | 50 y | 1 | 25 y | 3 | 15 I want to get his result: timestamp | xval | yval 1 | 30 | 25 3 | 50 | 15 2 | 40 | NULL But with this query I got a double unwanted result: select x.timestamp, x.value as xval, y.value as yva...
0

votes
0

answer
15

Views

Making a selection of itms that are the resault of (a positive join of two tables over a third)and (a negative join of two tables over a third)

I want to get a selection of all Articles (ART.ARTKEYI) that are assigned to an Element (PRO.PROKEYI), but whose Element is not assigned to any shop (PSZ.PSZPROKEYI). the DB is structured so: Table AEZ connects the Articles (ART.ARTKEYI = 1234) and the Elements (PRO.PROKEYI = 9876) AEZ: AEZPROKEYI...
Bob Bobson
0

votes
1

answer
24

Views

How to ORDER BY a column from INNER JOIN

Table structure from comments: id | user_id | cmt_id | slug 1 | 565 | 5 | home 2 | 324 | 6 | home 3 | 71 | 7 | home 4 | 408 | 1 | about Table structure from cmt_likes: id | user_id | cmt_id | slug 1 | 324 | 6 | home 2 | 324 | 6 | home 3 | 32...
nobody
0

votes
0

answer
6

Views

Two dataframes, same traits but different ID of individuals. Is there an R function to generate the conversion between old and new ID?

I'm working on a large dataset with a set of variables. I've downloaded a more recent dataset with the same variables but different coding for the samples IDs. I need to write a function that tells which of ID corresponds to the new one. Old_data old_ID var1 var2 var3 var4 var5 var6 1 A 2...
0

votes
4

answer
31

Views

Using subquery in join clause instead of column name

I am trying to join a two table. One column exists in the table but the other is a derived one. event_date - exists directly in the table deploy_date - derived using case statement See line #6 in the original query. 1 SELECT ab.id, 2 ab.event_date, 3 CASE 4...
Pirate X
0

votes
3

answer
19

Views

Different JOIN values depending on the value of another column

I have 2 tables j and c. Both tables have columns port and sec. For j.port = ABC, I want to join the 1st 6 characters of c.sec with the 1st 6 characters of j.sec. For other j.ports, I want to join c.sec = j.sec How can I do that ? select c.port,j.port,c.sec,j.sec from j, c where c.SEC = CASE WHEN...
faujong
1

votes
3

answer
1.7k

Views

Inner Join between table and a subquery on the same table

SQL server 2012. Edit: my original query was more complex than it should be, since I tried to do a Distinct query on a subset of the fields in the table and join that on the table itself to get the other (text) fields. the following query does the trick as well: SELECT DISTINCT p1.id ,p1.Name ,CAST(...
Mark Hagers
1

votes
1

answer
68

Views

Java thread join()

If I have one array of 10 thread ready 'to be used' and if I have something like for (int i=0;i< MyThreadArray.length: i++){ MyThreadArray[i].start() MyThreadArray.join() } If the thread #6 has a sleep() of 5 minutes, how can I run the thread #7 before the end of the #6? I have to wait for the end...
Mauro
1

votes
3

answer
36

Views

Run a JOIN statement that excludes duplicate rows

I have a table with duplicated entries (they have the same uid). I want to remove the duplicates from the query result by using a GROUP BY clause (one of the entries is valid, but it is random. I can only find out what the value is by joining it with db2 via rid. I am using an aggregate function (MA...
Sebastian
1

votes
2

answer
40

Views

Is it possible to insert row into the child table of FOREIGN KEY?

I have three tables in my database: COMPANY(ID, NAME) COUPON(ID, TITLE) COMPANY_COUPON(COMPANY_ID, COUPON_ID) Company can create coupons, so when it creates coupon, coupon's ID and ID of the company will be added into COMPANY_COUPON table. When company deletes coupon it also will be deleted fr...
Vladimir
1

votes
1

answer
37

Views

Sql server - Inner Join syntax with two joins

I've noticed one of our programmers wrote this: SELECT * FROM Table_A A INNER JOIN Table_B B INNER JOIN Table_C C ON C.Id = B.Id ON B.Id = A.Id I didn't expect this to work but it does return results from our database. Does this work the same as the normal join syntax? I can't find documentation on...
Carra
1

votes
4

answer
52

Views

SQL - nested joins

I have a mySQL database in which I'm trying to output a list of users and their assigned, administrative roles. My tables look something like: Users: ------- - id - fname - lname Role_Names ----------- - rn_id - role_name Roles --------- - role_id - user_id here's some data: Users: ------- 1 'Chris...
Daveh0
1

votes
2

answer
33

Views

MySQL complex query - SUM over several databases using UNION ALL and internal JOINs

I want to calculate a SUM over several databases using UNION ALL and internal JOINs. The MySQL user has the permissions to access all databases relevant. Here is my SQL query code so far: SELECT SUM(summen.OP1OPVerfahren = '0') AS 'Keine Operation durchgeführt', SUM(summen.OP1OPVerfahren = '1') AS...
mtjmohr
1

votes
1

answer
20

Views

Mysql count left join strange result

Can someone help me to understand those results ? (For me all 3 should return 6455). (Using RDS mysql-8.0.13) SELECT COUNT(p.product_id) FROM product p LEFT JOIN product_attributes pa ON p.pdt_id = pa.pdt_id WHERE pa.code = 'season'; Results : 6332 SELECT COUNT(*) FROM product p; Results : 6455 SELE...
pcouaillier
1

votes
2

answer
39

Views

Mysql Left Join Condition on column value starts with

I am trying to left join two different tables depending on which character a value in column Type starts with. For example: If the value of column type starts with 's' => Left Join table a If the value of column type starts with 'g' => Left Join table b How do I create this condition?
dirk r
0

votes
2

answer
22

Views

Optimize speed of Mysql JOIN query

I have 2 tables called T1 made of 1.6mln of rows and T2 made of 4.6mln of rows with with one-to-many relationship. The CREATE STMT of T1 is: CREATE TABLE `T1` ( `field_1` text, `field_2` text, `field_3` decimal(10,6) DEFAULT NULL, `field_4` decimal(10,6) DEFAULT NULL, `field_4` decimal(10,6) DEFAULT...
UgoL
1

votes
1

answer
27

Views

SQL - How to includes row with zero results with a WHERE condition

I have a query for selecting the number of people who reserved a specific hotel on all dates available. Basically, I have two tables: dates_event which list all the dates available in which I have 3 dates registered_hotels which list the registrations from all people with the according hotel, date a...
Gawet
1

votes
2

answer
5.8k

Views

Rewriting subquery as JOIN?

UPDATE: Thanks to Sifu Bill's advice I have amended the SQL query. Now it returns the correct number of distinct assets (five). Is it possible to rewrite the following subquery as a JOIN? SELECT TOP 100 PERCENT Asset_ID, work_order_id, status_id, downtime_hours, date_completed FROM dbo.mtvw_wo_reaso...
Greenhorn
1

votes
2

answer
2.6k

Views

PostgreSQL: LEFT JOIN creates blank row

See important new discoveries 1 and 2 at end of this explanation. I am running Postgres 9.1.3 and am having a weird left join issue. I have a table named consistent.master with over 2 million rows. It has a column named citation_id, and that column has no nulls. I can verify that with this: SELECT C...
Aren Cambre
1

votes
2

answer
24

Views

Join on tables that have multiple records against same id

I have two tables: applicant_personal_info looks like this: A_id FirstName LastName 1 Fahad Subzwari applicant_professional_info table looks like this: A_id JobOrganization JobDuration TotalExperience 1 10Pearls 12 Months...
Fahad_SUbzwari
1

votes
1

answer
106

Views

My-sql one to many relation join get sum of left table column [duplicate]

This question already has an answer here: Where clause for only one table in a left join 4 answers Get all results from first table in a MySQL join 2 answers Return default result for IN value regardless 2 answers When we have two tables with one to many relation how we can get the sum of left ta...
Chythi
1

votes
2

answer
215

Views

SYBASE :Join Two table where Value of one table column is column name of other table

Table1 PRICE ID_1 ID_2 ID_3 500 1 2 3 750 2 3 4 Table2 ID VALUE ID_1 1 ID_2 2 ID_3 3 I have two tables and want to join these tables like Select * from table1 T1 Join Table2 T2 on T1.(T2.ID) = T2.Value In short I want to convert one table co...
gaurav bharadwaj
1

votes
1

answer
38

Views

Access UPDATE statement in query fails to actually update

The following tables exist: Passerine_Survey_Observation Species_Codes I'm trying to set the common name in Passerine_Survey_Observation with that of Species_Codes: UPDATE Passerine_Survey_Observation INNER JOIN Species_Codes ON Passerine_Survey_Observation.SPEC_FK = Species_Codes.SPEC SET Passeri...
Kevin R. M.
1

votes
2

answer
109

Views

Improving MySQL SELECT query speed of four huge (~100M rows) JOINed tables

I have the following query that is supposed to find extra information for each related record by a given linker number. For example this query finds Title, Author, and Date for all the documents that linkTo a single one with linker=86sgv_ksg:0040608. SELECT `r`.`linker`, IF(`s`.`isSecond`='1', `c2...
SAVAFA
1

votes
1

answer
112

Views

Ahoy Ruby on Rails- join tables (Visit and Event)

I am using Ahoy for tracking in my web app. I find events with this: @events = Ahoy::Event.where_properties(title: params[:token]) and I want to get all Visits having visit ID that the above relation, @events, has. I can join the two tables using Visit.joins(:ahoy_events) but Visit.joins(:@events) g...
a3y3
1

votes
0

answer
47

Views

Using SUM and Count function

I would like to group sum and count according to months and my query as below SELECT terminal_name, sum(case when month(sales_timestamp) = 1 then nc_deposit else 0 end) as jan_amount, count(case when month(sales_timestamp) = 1 then nc_deposit else 0 end) as jan_count FROM dbfastshosted.dbo.fh_mf_...
nur wahidah
1

votes
0

answer
28

Views

display data from the same database colomn into 2 different jtable colomns using self join

I want to display data from the same database colomn into 2 different jtable colomns using that self join from the statement. I'm pretty sure my problem is somewhere here but I don't know how to solve it: while (rs.next()) { String d = rs.getString('a.nrzbor'); String e = rs.getString('b.nrzbor'); m...
Andrei Jitaru
1

votes
1

answer
385

Views

Why is there a result of `#Error` in an Access query with a join on two type-converted fields?

I’m getting a result of #Errorin an Access 2010 query which has a right join on two fields which are type-converted. I can eliminate the error by improving my queries, but I’m confused as to why it happens. I have the following two tables, T1 and T2. Both consist of an ID field and a text field....
Scott H
1

votes
1

answer
26

Views

Join head table with position table with laravel eloquent

I have the following construction: (Laravel Version 5.4) I have a head table (series), and a table with associated positions (posts). The positionstable has three values whose determine, whether the posts should be displayed, or even not. The series table has this information as well. These columns...
Markus Lenz
1

votes
0

answer
222

Views

Left join big DataTables in C#

I have 3 big tables in SQL Server: Table1 (ID1, ..., ID2) => around 3 million rows Table2 (..., ID1, ...) => around 50000 rows Table3 (..., ID2, ...) => around 8 million rows The first table is the 'main' table, the second table has the foreign key to the first one (ID1), and the third table has a f...
HermioneLumus
1

votes
1

answer
54

Views

How to perform a join on two tables in an SSIS package, where the table names are dynamic

Just for reference, this question is more or less a follow-up to this question which I asked a few weeks ago. In that case I suspect the problem was essentially that I had picked up someone else's incomplete code and was trying to update it without completely understanding what they'd been trying t...
Nick
1

votes
1

answer
187

Views

Fluent NHibernate: How to perform INNER JOIN?

I was wondering if someone could help me. I want to perform SQL INNER JOIN operation by using NHibernate. First of all let me introduce you to the structure of my database. I have the following parameters into my C# method: int documentId, int userId, int folderId. My main goal is to get RoleDocume...
Joseph Katzman

View additional questions