Solved

join on tables not working as expected MySQL

Posted on 2014-11-19
7
240 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…

761 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