This Query creates THOUSANDS of Duplicates!!

[Mark]

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

Thanks.

 

[J. Clay]

Hi Mark,

I have do a lot of Access/Goldmine integration for our company. It can get
interesting at times.

Your problem is that your INNER JOIN ON is linking everything that doesn't
match, therefore you are getting a TON of records returned. It looks like
you are trying to see what records are in your "Customers" table that is not
in your GM List all based on the street address. If this is the case try
the following:

SELECT Customers.*
FROM Customers LEFT JOIN [GM Customer List] ON customers.Street = [GM
Customer List].Address1
WHERE [GM Customer List].Address1 IS NULL

This will find all of the records in Customers that don't exist in GM
Customer List

HTH,
Jim

 

[John Vinson]

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

What this will do is pair every record in [Customers] with every
record in [GM Customer List] which has a value in ADDRESS1 different
than the value of STREET. So each [Customer] will be paired with
thousands of list members!

You don't say what you want this query to accomplish, but I'm guessing
that you want to return all records in Customers whose address is
*not* in the GM Customer List. If that's the case, either use the
Unmatched Query Wizard from the "new queries" list, or use a
"frustrated outer join" query:

SELECT customers.*
FROM customers
LEFT JOIN [GM Customer List]
ON customers.STREET=[GM Customer List].ADDRESS1
WHERE [GM Customer List].[Address1] IS NULL
WITH OWNERACCESS OPTION;


John W. Vinson[MVP]

 

[Immanuel Sibero]

Mark,


The SQL you listed will produce duplicates because of the relational
operator you're using (i.e. <> inequality).
In your post, you didnt really state what you want. You'll have to define
what you want. My guess is you have two lists (i.e. tables) - Customers and
[GM Customer List]. And you want a list of customers who have different
addresses in the two tables? But again, this is a guess.

If my guess was correct, then you would need to relate the two tables on
some "common" identifying field from both tables (i.e. CustomerID). Lets
assume both of your tables have an identifying field called ID. So,
something like:

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.ID = [GM Customer
List].ID
WHERE customers.STREET <> [GM Customer List].ADDRESS1
WITH OWNERACCESS OPTION;

Goldmine's implementation of SQL is limited but enough to get you to do a
lot of different queries. If you find yourself building SQL queries in
Goldmine frequently, I recommend creating an Access .mdb with linked tables
to Goldmine's data (i.e. whether dbase or SQLserver implementation). You
would have to check with Goldmine's documentation to gain understanding of
Goldmine design i.e. relationships among the various tables - Contact1,
Contact2, Contsupp, etc etc. It's not that complicated. You can then use
Access to build your queries using the built in query designer. Doing it
this way is almost like having a query builder wizzard for Goldmine.

HTH,
Immanuel Sibero

 

[Mark]

You are a saint. That is exactly what I am trying to do! I have been
stressing on this for some time now. I'm going to jump in and run it right
now! Then I need to import the customers that are not in the GM list into
GM. Can I do that with this Query?

Thanks!

Hi Mark,

I have do a lot of Access/Goldmine integration for our company. It can
get
interesting at times.

Your problem is that your INNER JOIN ON is linking everything that doesn't
match, therefore you are getting a TON of records returned. It looks like
you are trying to see what records are in your "Customers" table that is
not
in your GM List all based on the street address. If this is the case try
the following:

SELECT Customers.*
FROM Customers LEFT JOIN [GM Customer List] ON customers.Street = [GM
Customer List].Address1
WHERE [GM Customer List].Address1 IS NULL

This will find all of the records in Customers that don't exist in GM
Customer List

HTH,
Jim

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL
Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

Thanks.

 

[Mark]

It doesn't allow me to "LEFT JOIN" it says invalid join

Hi Mark,

I have do a lot of Access/Goldmine integration for our company. It can
get
interesting at times.

Your problem is that your INNER JOIN ON is linking everything that doesn't
match, therefore you are getting a TON of records returned. It looks like
you are trying to see what records are in your "Customers" table that is
not
in your GM List all based on the street address. If this is the case try
the following:

SELECT Customers.*
FROM Customers LEFT JOIN [GM Customer List] ON customers.Street = [GM
Customer List].Address1
WHERE [GM Customer List].Address1 IS NULL

This will find all of the records in Customers that don't exist in GM
Customer List

HTH,
Jim

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL
Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

Thanks.

 

[Mark]

Never mind. It was a spacing issue when I pasted.

It doesn't allow me to "LEFT JOIN" it says invalid join

Hi Mark,

I have do a lot of Access/Goldmine integration for our company. It can
get
interesting at times.

Your problem is that your INNER JOIN ON is linking everything that
doesn't
match, therefore you are getting a TON of records returned. It looks
like
you are trying to see what records are in your "Customers" table that is
not
in your GM List all based on the street address. If this is the case try
the following:

SELECT Customers.*
FROM Customers LEFT JOIN [GM Customer List] ON customers.Street = [GM
Customer List].Address1
WHERE [GM Customer List].Address1 IS NULL

This will find all of the records in Customers that don't exist in GM
Customer List

HTH,
Jim

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL
Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

Thanks.

 

[J. Clay]

What you will need to do is to put this into a table and use the GM Import
capabilities. You will need to convert it to either a DBF file or Text file
as GM does not use ODBC.

HTH,
Jim

You are a saint. That is exactly what I am trying to do! I have been
stressing on this for some time now. I'm going to jump in and run it right
now! Then I need to import the customers that are not in the GM list into
GM. Can I do that with this Query?

Thanks!

Hi Mark,

I have do a lot of Access/Goldmine integration for our company. It can
get
interesting at times.

Your problem is that your INNER JOIN ON is linking everything that doesn't
match, therefore you are getting a TON of records returned. It looks like
you are trying to see what records are in your "Customers" table that is
not
in your GM List all based on the street address. If this is the case try
the following:

SELECT Customers.*
FROM Customers LEFT JOIN [GM Customer List] ON customers.Street = [GM
Customer List].Address1
WHERE [GM Customer List].Address1 IS NULL

This will find all of the records in Customers that don't exist in GM
Customer List

HTH,
Jim

SELECT customers.*, *
FROM customers INNER JOIN [GM Customer List] ON customers.STREET<>[GM
Customer List].ADDRESS1
WITH OWNERACCESS OPTION;


What do I need to do here??

I am just using the wizard, but I do have little knowledge of SQL
Queries.
Just not sure what to do with these. also, I want to export the Query
results to a new table/database/excel sheet. They are to be imported to
another program.

Thanks for any help/suggestions. My SQL experience is in a CRM program
called GoldMine which has limited SELECT query functions.

Thanks.