Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Query problem on join

Posted on 2009-04-02
3
Medium Priority
?
231 Views
Last Modified: 2013-12-12
I have a simple query running against MySQL through PHP.  I want to insert into a table all values that are in another table but not in that table.  This is the query I have:

INSERT Into DataNew (CollectorCode,CollectGross)
SELECT DISTINCT DataHistory.CollectorCode, 0
FROM DataHistory
LEFT OUTER JOIN DataNew ON DataNew.CollectorCode = DataHistory.CollectorCode
WHERE DataHistory.CodeType =1
AND DataHistory.CollectorCode NOT LIKE '%-b'
AND DataNew.CollectorCode IS NULL

The trouble is that the select query returns 602 rows whether I fileter by the DataNew.CollectorCode is Null or not.  How can I go about insertings into DataNew all Collectorcodes from DataHistory which are not already present?
0
Comment
Question by:GeoffSutton
  • 2
3 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 24052807
Hi,

I would do like this (if CollectorCode is unique)

INSERT Into DataNew (CollectorCode,CollectGross)
SELECT DISTINCT dh.CollectorCode, 0
FROM DataHistory dh
WHERE NOT EXISTS (SELECT 1 FROM DataNew dn WHERE dn.CollectorCode = dh.CollectorCode)

/peter
0
 
LVL 10

Author Comment

by:GeoffSutton
ID: 24053143
Unfortunately that does not work.  You query:
SELECT DISTINCT dh.CollectorCode, 0
FROM DataHistory dh
WHERE NOT EXISTS (SELECT 1 FROM DataNew dn WHERE dn.CollectorCode = dh.CollectorCode) AND
dh.CodeType=1
Returns the exact same results as
SELECT DISTINCT dh.CollectorCode, 0
FROM DataHistory dh
WHERE dh.CodeType=1

 They both show 602 rows, and datanew has 149 distinct codes entered.  I was reasonably sure that the SQL and the logic were good.  But being unfamiliar with MySQL I am wondering if the comparisons pay attention to potential trailing spaces and hidden characters?  I know that the collation is case insensitive.
Thanks for the quick response.  Hopefully we can resolve this right away.
Geoff
 
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 24053248
Just so I understand, CollectorCode is unique?

Can you show us the tabledefinition and some sampledata?

If you would like to trim the CollectorCode try

SELECT DISTINCT dh.CollectorCode, 0
FROM DataHistory dh
WHERE NOT EXISTS (SELECT 1 FROM DataNew dn WHERE TRIM(dn.CollectorCode)=TRIM(dh.CollectorCode)) AND
dh.CodeType=1
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

971 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