0

I need to get list of address form specifc customer group, below is condition; 1) firstname & lastname should be unique in customer address group.

I have tried below but it is not working:

SELECT cae.*, cg.customer_group_id, DISTINCT(GROUP_CONCAT(caev.value))
FROM customer_address_entity cae, customer_group cg, customer_entity ce, customer_address_entity_varchar caev
WHERE ce.group_id=cg.customer_group_id 
AND ce.entity_id=cae.parent_id 
AND ce.entity_id=caev.entity_id
AND cg.customer_group_code IN ('group1', 'group2')
AND caev.attribute_id IN (20,22)
GROUP BY cae.parent_id;

Here attribute_id 20 & 22 is for firstname & lastname;

Here is table structure for each tables:

mysql> desc customer_address_entity;
+------------------+----------------------+------+-----+---------------------+-----------------------------+
| Field            | Type                 | Null | Key | Default             | Extra                       |
+------------------+----------------------+------+-----+---------------------+-----------------------------+
| entity_id        | int(10) unsigned     | NO   | PRI | NULL                | auto_increment              |
| entity_type_id   | smallint(5) unsigned | NO   |     | 0                   |                             |
| attribute_set_id | smallint(5) unsigned | NO   |     | 0                   |                             |
| increment_id     | varchar(50)          | YES  |     | NULL                |                             |
| parent_id        | int(10) unsigned     | YES  | MUL | NULL                |                             |
| created_at       | timestamp            | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at       | timestamp            | NO   |     | 0000-00-00 00:00:00 |                             |
| is_active        | smallint(5) unsigned | NO   |     | 1                   |                             |
+------------------+----------------------+------+-----+---------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> desc customer_group;
+---------------------+----------------------+------+-----+---------+----------------+
| Field               | Type                 | Null | Key | Default | Extra          |
+---------------------+----------------------+------+-----+---------+----------------+
| customer_group_id   | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| customer_group_code | varchar(32)          | NO   |     | NULL    |                |
| tax_class_id        | int(10) unsigned     | NO   |     | 0       |                |
+---------------------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc customer_entity;
+---------------------------+----------------------+------+-----+---------------------+-----------------------------+
| Field                     | Type                 | Null | Key | Default             | Extra                       |
+---------------------------+----------------------+------+-----+---------------------+-----------------------------+
| entity_id                 | int(10) unsigned     | NO   | PRI | NULL                | auto_increment              |
| entity_type_id            | smallint(5) unsigned | NO   | MUL | 0                   |                             |
| attribute_set_id          | smallint(5) unsigned | NO   |     | 0                   |                             |
| website_id                | smallint(5) unsigned | YES  | MUL | NULL                |                             |
| email                     | varchar(255)         | YES  | MUL | NULL                |                             |
| group_id                  | smallint(5) unsigned | NO   |     | 0                   |                             |
| increment_id              | varchar(50)          | YES  |     | NULL                |                             |
| store_id                  | smallint(5) unsigned | YES  | MUL | 0                   |                             |
| created_at                | timestamp            | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at                | timestamp            | NO   |     | 0000-00-00 00:00:00 |                             |
| is_active                 | smallint(5) unsigned | NO   |     | 1                   |                             |
| disable_auto_group_change | smallint(5) unsigned | NO   |     | 0                   |                             |
+---------------------------+----------------------+------+-----+---------------------+-----------------------------+
12 rows in set (0.00 sec)

mysql> desc customer_address_entity_varchar;
+----------------+----------------------+------+-----+---------+----------------+
| Field          | Type                 | Null | Key | Default | Extra          |
+----------------+----------------------+------+-----+---------+----------------+
| value_id       | int(11)              | NO   | PRI | NULL    | auto_increment |
| entity_type_id | smallint(5) unsigned | NO   | MUL | 0       |                |
| attribute_id   | smallint(5) unsigned | NO   | MUL | 0       |                |
| entity_id      | int(10) unsigned     | NO   | MUL | 0       |                |
| value          | varchar(255)         | YES  |     | NULL    |                |
+----------------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

If someone can provide solution using magento 1 collection join that would be better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.