Solved

join on tables not working as expected MySQL

Posted on 2014-11-19
7
235 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 40

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now