Solved

join on tables not working as expected MySQL

Posted on 2014-11-19
7
241 Views
Last Modified: 2014-11-30
I am trying to join 2 tables and get the one with the grater date

Select ToolType,SerialNumber,Qty From Temp_MyTools Where FUploadTime > HUploadTime and SerialNumber NOT IN(
select ti.SerialNumber 
From Temp_IDs AS  ti 
LEFT Join Temp_MyTools as tm on tm.serialnumber = ti.serialnumber 
WHERE tm.FUploadTime > ti.UploadDate) 

Open in new window


tm.FUploadDate for 3 records is not  > ti.UploadDate Why is it pulling those serialnumbers in the subselect
0
Comment
Question by:r3nder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40453992
Hello,

can you post sample data for those 3 records?
0
 
LVL 6

Author Comment

by:r3nder
ID: 40454143
Here are the records in the subselect
Temp-IDs.csv
0
 
LVL 6

Author Comment

by:r3nder
ID: 40454146
here are the records I am comparing them to
Temp-MyTools.csv
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 41

Expert Comment

by:Sharath
ID: 40454330
Can you post the expected result also?
0
 
LVL 6

Author Comment

by:r3nder
ID: 40454351
the expected results should be what is in Temp_MyTools.csv minus what is in  Temp_Ids.csv because the uploaddate in the Temp_MyIDs is greater than the FuploadTime in the Temp_MyTools table
0
 
LVL 6

Accepted Solution

by:
r3nder earned 0 total points
ID: 40454354
Now that I  wrote it out wouldn't it just be this to get those serial numbers
Select ToolType,SerialNumber,Qty From Temp_MyTools Where FUploadTime > HUploadTime and SerialNumber NOT IN(
select ti.SerialNumber 
From Temp_IDs AS  ti 
LEFT Join Temp_MyTools as tm on tm.serialnumber = ti.serialnumber 
WHERE ti.UploadDate > tm.FUploadTime) 

Open in new window

0
 
LVL 6

Author Closing Comment

by:r3nder
ID: 40472367
figured it out myself
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question