Jump to content

SQL Query


phytopet

Recommended Posts

Hi,

 

Sorry of this is the wrong area to post, however pretty new to this.

I was wanting to run an SQL query so that I can export both my customer and address data in one csv file?

When I have simply exported each entity they are in separate csv files and I can't match up the data for customer and address as the email is only displayed within customer csv.

 

Any help/advice is greatly appreciated

Thanks

 

Link to comment
Share on other sites

That is an impossible question. You will need to make choices.

A customer can have as much addresses as he wants. It is only the orders that have a restriction: they have a delivery and a billing address. I have seen some customers making a new address record for every order. Obviously they didn't understand the system very well. 

So the first decision that you will need to make when creating your csv is how many addresses you want and how you want to select them.

After that it is just a matter of programming. You can either do it with PHP or you can run a query that creates an auxiliary table.

Link to comment
Share on other sites

Hi musicmaster, thanks for the reply.

 

What I need is one output that shows links the data from the customer & address tables so that I have all of the data displayed on single rows.

I wanted to have a spreadsheet that contains something like;

First Name Last Name Email Company Address1 Address2 City Province Province Code Country Country Code Zip Phone Accepts Marketing Total Spent Total Orders Tags Note Tax Exempt

 

but as the data is split across 2 tables I cant link the correct email with customer name and address?

 

I ran the sql query,

 Select *
    FROM ps_address
    INNER JOIN ps_customer
          ON ps_address.Firstname = ps_customer.Firstname AND ps_address.Lastname = ps_customer.Lastname 

but as you mentioned, there were several address listed for the same customer?

 

Is there a way that I can return the data so that emails are not duplicated and therefore only have one address?

Edited by phytopet (see edit history)
Link to comment
Share on other sites

It sounds like you evade the question. All those addresses are valid. So you have to decide (or leave it to fate) which address you get.

One possibility - that lets the computer choose the address - is:

Select DISTINCT c.id_customer, c.*,a.*
    FROM ps_customer c
	   INNER JOIN ps_address a
          ON a.id_customer = c.id_customer

But this won't work in the newer versions of MySql (it does work with MariaDb).

Another approach is to ask for the last address:

Select DISTINCT c.id_customer, c.*,a.*
    FROM ps_customer c
	   INNER JOIN ps_address a
          ON a.id_customer = c.id_customer AND a.id_address = (SELECT MAX(id_address) FROM ps_customer WHERE id_customer=c.id_customer)

I write this last one from the top of my head and may be have made some mistake.

Link to comment
Share on other sites

@musicman Do you know what I would need to include the customer address from the address table with the following query:

 

SELECT c.`id_customer` AS `id_customer`, `id_gender`, `firstname`, `lastname`, c.`email` AS `email`, `birthday`, `date_add`, c.`active` AS `active` , c.*, a.id_group FROM `ps_customer_group` a LEFT JOIN `ps_customer` c ON (a.`id_customer` = c.`id_customer`) WHERE 1 AND a.`id_group` = 3 AND c.`deleted` != 1 AND c.id_shop IN (1) ORDER BY c.`email` asc LIMIT 0, 1000
 

Thanks 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...