SQL Query

[phytopet]

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

 

[musicmaster]

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.

[phytopet]

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 February 1, 2018 by phytopet (see edit history)

[musicmaster]

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.

[phytopet]

@musicmaster Thanks so much, that was exactly what I was looking for! sorry for the vague query, SQL is very much brand new to me so thanks for your help! 

[phytopet]

@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