Inner Join Query – VariableTime

Hi

I have two tables
meteor_Events and video_Events and the common fields are Date and Time

I want to search meteor_events where the date and time match any video events so using this query it works fine

SELECT
    meteor_events.event_id,
    meteor_events.fname,
    meteor_events.date,
    meteor_events.Time
FROM
    Video_Events
    INNER JOIN
    meteor_events
    ON
        Video_Events.Date = meteor_events.date AND
        Video_Events.Time = meteor_events.Time

Code (SQL):

I now want to refine this and bring back results where the date matches but the time matches if the meteor_events.Time is plus or minus 2 seconds
SEMrush

ie if the Video_Events.Time is say 00:12:34 bring back results if the meteor_events.Time is between 00:12:32 and 00:12:36

Pointers appreciated

Kind Regards
John Berman

 

Google SpreadSheets query: how to join “grouped by” cells in a list?

Suppose I have this table:

| Name   | Amount | Category |
| name_1 | 10     | A        |
| name_2 | 2      | B        |
| name_3 | 3      | B        |
| name_4 | 6      | C        |

Thanks to this answer, I know how to write a query that groups the rows by category, and gives me sum of Amount by category, like this:

=QUERY(A2:C5,"select C, sum(B) group by C order by sum(B)")

enter image description here

My question is: how do I add a third row to my query, such that it lists the Name of every row in that group (possibly, but not necessarily, comma-separated)?

In other words, I’d like the result to look like this:

|   | sum | items          |
| B | 5   | name_2, name_3 |
| C | 6   | name_1         |
| A | 10  | name_4         |

sql server – duplicate values with left join

I am getting more rows then I expect returned by my query.

I believe it has something to do with my join statements.

There are multiple tables that have different information in them from SAGE X3 System

SELECT 
GL.LEDTYP_0 AS 'Ledger Type',
APL.LANMES_0 AS 'Ledger_Type_Description',
GL.LED_0 AS 'Ledger_Code',
A9.TEXTE_0 AS 'Ledger_Code_Description',
GL.COA_0 AS 'Chart_Of_account_Code',
A10.TEXTE_0 AS 'Chart_Acct_Description',
Company.CPYNAM_0 AS 'Company_Description',
GL.FIY_0 AS Fiscal_Year,
GL.CPY_0 AS 'Company_Code',
GL.PER_0 AS PERIOD,

GL.OFFACC_0 AS 'Partner_Code',
Partner.BPRNAM_0 AS 'Partner_Description',
GL.ACC_0 AS 'Account Code', 
A11.TEXTE_0 AS 'Account Description',
GL.NUM_0 AS 'Document_Number', 
GL.DES_0 AS 'Document Description',
GL.CCE_0 'Value Stream', 
A.TEXTE_0 AS 'Value Stream Name',
GL.CCE_1 AS 'Product_Line',
A1.TEXTE_0 AS 'Product_Line_Name',
GL.CCE_2 AS 'CropYear',
A2.TEXTE_0 AS 'CropYear Name',
GL.CCE_3 AS Project,
A3.TEXTE_0 AS 'ProjectName',
GL.CCE_4 AS Department,
A4.TEXTE_0 AS Department_Name,
GL.ACCDAT_0 AS AccountingDate, 
Format(GL.PSTLED_0, 'C') AS 'Ledger_Posting',

GL.CAT_0 AS 'Journal_Category',
GL.NEWFIYNUM_0 AS 'NewFiscalYear',
GL.NEWPERNUM_0 AS 'NewFiscalPeriod'

FROM
(SEI_LIVE).(GL_TX_WITH_OB_VIEW_SEP_AUG) GL
LEFT JOIN
COMPANY Company
ON GL.CPY_0 = Company.CPY_0
LEFT JOIN
GACCOUNT GA
ON GL.ACC_0 = GA.ACC_0
LEFT JOIN
PINVOICED INVOICED
ON GL.NUM_0 = INVOICED.NUM_0
LEFT JOIN
APLSTD APL
ON GL.LEDTYP_0 = APL.LANNUM_0
AND APL.LANCHP_0 = ‘2644’
AND LAN_0 = ‘ENG’
LEFT JOIN
BPARTNER Partner
ON GL.OFFACC_0 = Partner.BPRNUM_0
LEFT JOIN
ATEXTRA A10
ON GL.COA_0 = A10.IDENT1_0
AND A10.CODFIC_0 = ‘GCOA’
AND A10.ZONE_0 = ‘DESTRA’
AND A10.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A11
ON GL.COA_0 = A11.IDENT1_0
AND GL.ACC_0 = A11.IDENT2_0
AND A11.CODFIC_0 = ‘GACCOUNT’
AND A11.ZONE_0 = ‘DESTRA’
AND A11.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A
ON GL.DIE_0 = A.IDENT1_0
AND GL.CCE_0 = A.IDENT2_0
AND A.CODFIC_0 = ‘CACCE’
AND A.ZONE_0 = ‘DESTRA’
AND A.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A1
ON GL.DIE_1 = A1.IDENT1_0
AND GL.CCE_1 = A1.IDENT2_0
AND A1.CODFIC_0 = ‘CACCE’
AND A1.ZONE_0 = ‘DESTRA’
AND A1.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A2
ON GL.DIE_2 = A2.IDENT1_0
AND GL.CCE_2 = A2.IDENT2_0
AND A2.CODFIC_0 = ‘CACCE’
AND A2.ZONE_0 = ‘DESTRA’
AND A2.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A3
ON GL.DIE_3 = A1.IDENT1_0
AND GL.CCE_3 = A1.IDENT2_0
AND A3.CODFIC_0 = ‘CACCE’
AND A3.ZONE_0 = ‘DESTRA’
AND A3.LANGUE_0 = ‘ENG’
LEFT JOIN
ATEXTRA A4
ON GL.DIE_4 = A4.IDENT1_0
AND GL.CCE_4 = A4.IDENT2_0
AND A4.CODFIC_0 = ‘CACCE’
AND A4.ZONE_0 = ‘DESTRA’
AND A4.LANGUE_0 = ‘ENG’

LEFT JOIN
    ATEXTRA A9 
    ON GL.LED_0 = A9.IDENT1_0 
    AND A9.CODFIC_0 = 'GLED' 
    AND A9.ZONE_0 = 'DESTRA' 
    AND A9.LANGUE_0 = 'ENG' 

WHERE
—GL.ACC_0 = ‘17000’
YEAR(GL.ACCDAT_0) > 2019

How do I join different table columns into one column without concatenating contents in MySQL?

Table_1

policy_number_a policy_number_b policy_number_c
pd0401 NULL NULL
NULL pf0809 tc2001
NULL NULL tc3021

and so on.
I want the table to look like this

policy_name policy_number
a pd0401
b pf0809
c tc3021

The output table can retail NULL values.

VISA situation where I am currently working in France and going to join Germany soon [migrated]

I am an Indian with a residence talent permit of France valid till Sep end 2021. I am currently in France. I have a job offer from Germany starting Sep 1, 2021. My questions are :

  1. Do I need to go back to India and apply for German Visa from there ?

  2. If I can apply from inside France, then what will happen to my French residence permit, provided Germany issues me a VISA ? Isn’t it a situation where I have a visa from Germany and a residence permit of France ?

  3. Where may I find official information about this situation ?

sql server – What does Nested Loops join operator has to do with a Scalar?

As far as I understand the docs, the Nested Loops operator is a join operator, i.e., it requires two tables as input.

If that’s correct, then why the Nested Loops operator is used with a Scalar input?

For example, take the following query (from Paul White’s website) and its execution plan.
You can see that the inputs for the Nested Loops operator are (1) a Scalar, and (2) a table (the result of the Index Seek).

I know that a scalar can’t be joined with a table, then what that actually means? What’s actually being joined?

USE AdventureWorks2019;
DECLARE @Like nvarchar(50) = N'D%';
SELECT p.(Name)
FROM   Production.Product AS p
WHERE  p.(Name) LIKE @Like;

enter image description here

BTW, I presume that it’s a pretty basic question, but I couldn’t find a good resource that goes through such basics methodologically, so a recommendation would be much appreciated.

When did you join FP? | Forum Promotion

Want to donate towards something I run, or perhaps to just say “thank you”? Consider sending me a tip via PayPal, or with Crypto! I don’t judge.

Feel free to join my support forum for various exchanges! Any support helps! Free Banner Exchange Network Support
Want to list your forum in the Forum Stock Market? Perhaps purchase stock in a forum? Click here to open the topic dedicated to it!

join – Trouble Combining Two Data Frames R Studio

I want to combine these two dataframes in to one. I want to have the MCN_ID’s stacked vertically and the UNS 1930 column to only be one column, not “UNS 1930.x” and “UNS 1930.y” which is what my code is currently resulting in. This is ultimately being done a large scale with multiple loops for this merge. Please help!

test1 <- data.frame("AABB", 1)
colnames(test1)(1) <- "MCN_ID"
colnames(test1)(2) <- "UNS 1930"

test2 <- data.frame("BBAA", 23)
colnames(test2)(1) <- "MCN_ID"
colnames(test2)(2) <- "UNS 1930"

test3 <- full_join(test1, test2, by = "MCN_ID")

Gives this result:

MCN_ID   UNS 1930.x   UNS 1930.y
AABB     1            NA  
BBAA     NA           23 

But I want this:

MCN_ID   UNS 1930
AABB     1  
BBAA     23 

java – Hibernate / JPA Why many-to-many join column id is false?

to-many tables mapped like this :

Resume.java :

 @ManyToMany(cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
        })
 @JsonIgnore
   @JoinTable(
           name = "resume_skills", 
           joinColumns = @JoinColumn(name = "skill_id"), 
           inverseJoinColumns = @JoinColumn(name = "resume_id"))
 private List<Skill> skills = new ArrayList<Skill>();


 public void addSkill(Skill skill) {
     skills.add(skill);
     skill.getResumes().add(this);
 }

Skill.java

   @ManyToMany(mappedBy="skills")
   @JsonIgnore
   private List<Resume> resumes;

I do this :

Resume resumeToAdd = new Resume(resume.getGithubAdress(),
            resume.getLinkedinAdress(),
            resume.getCoverLetter(),
            resume.getPicture(),
            employee
            );
    
    resumeDao.save(resumeToAdd);
    
    Skill skill = skillService.findById(1).getData();
    System.out.println(skill);
    resumeToAdd.addSkill(skill);
    resumeDao.save(resumeToAdd);

But it occurs an error, for example it added a Resume with id 60 and it’s trying to add mapping with skill id 60 But it cant because there is no id called 60 it should be 1. What did i miss here?

oracle – Get Join Keys dynamically from a table

SQL/Impala

I have a scenario where I need to join my input file with a mapping table which has map columns and derived columns. The join keys are dynamic and need to taken from a dynamic metadata table.

Metadata table can be modified by user to either update or insert new keys. The join happens in multiple passes. Pass1 will have one set of columns as keys , Pass2 will have another set of columns as join keys.

Pass2 derived values can overwrite Pass1 derived values. The derived columns are same through out.

Example:

Keys metadata table

enter image description here

Mapping Table

enter image description here

In

Input File
enter image description here

Join keys for Pass1 are A,B,C,D,E. Join will map values 10,20,30,40,50 from Input file to Values 10,20,30,40,50 in mapping table – DerivedValues1,2,3,4 will be derived.

Join Keys for Pass2 are A,B,C,D – Join will map values 10,20,30,40 from Input File to values 10,20,30,40 in mapping table – DerivedValues1,2,3,4 will be derived and can overwrite values derived in Pass1

In Pass3 – Column A and Derived Value3 will be join key. Join will map values 15, Jasmine to mapping table – DerivedValues1,2,3,4 will be derived.

Any idea how we could do this in SQL or Impala. Basically the platform is built in Big Data and we just need to write SQL like queries to implement this logic

Appreciate any help.

THanks
Nitin