Jump to content

Get list of customers by groups


kesaven

Recommended Posts

Hello

 

I am having difficulty in doing the following:

 

My tables are as follows:

vs_customer_group

vs_customer

 

One customer may belong to many groups.

When I select 2 or more groups I need to get only customers that belong to all the selected groups at the same time.

 

Below is my actual query:

SELECT distinct * FROM vs_customer c
INNER JOIN vs_customer_group g ON g.id_customer = c.id_customer
INNER JOIN vs_group_lang l on l.id_group = g.id_group
WHERE id_lang = 2 AND l.id_group IN('
.$_GET['ids'].')'

 

It is returning all customers who are on any of the selected group.

 

can you please help?

post-714656-0-66631400-1396021188_thumb.png

Link to comment
Share on other sites

The WHERE clause is specifying that records matching ANY of the group ids in the set should be selected.

 

EDIT 1: Removed rest of answer because it was wrong!!

 

EDIT 2: Having another go....

SELECT * FROM vs_customer c 
LEFT JOIN vs_customer_group g ON g.id_customer = c.id_customer 
INNER JOIN vs_group_lang l ON l.id_group = g.id_group 
WHERE c.id_lang = 2 
AND g.id_group IN('.$_GET['ids'].')
GROUP BY c.id_customer HAVING COUNT(DISTINCT g.id_group) = '.count($_GET['ids']) 
Edited by Paul C (see edit history)
Link to comment
Share on other sites

Hello Paul

 

Thanks for your response.

However the proposed solution is not working

 

It is actually excluding the customer which is actually in all the groups when it should have excluded groups where the customer is not IN

Link to comment
Share on other sites

Hello I have eventually found a solution to my problem:

 

$exsql = '
SELECT distinct c.id_customer
FROM vs_customer c
LEFT OUTER JOIN vs_customer_group g ON g.id_customer = c.id_customer
INNER JOIN vs_group_lang l on l.id_group = g.id_group
WHERE l.id_lang = 2
AND 1 =1 '
;

$idds = explode(",", $_GET['ids']);
foreach($idds as $idd)
{
$exsql = $exsql.' AND EXISTS (

SELECT *
FROM vs_customer
LEFT OUTER JOIN vs_customer_group g ON g.id_customer = vs_customer.id_customer
INNER JOIN vs_group_lang l on l.id_group = g.id_group
WHERE 1 =1
AND g.id_group ='
.$idd.'
AND vs_customer.id_customer = c.id_customer
)
'
;
}
//echo $exsql;
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($exsql);
$num_rows = mysql_num_rows($result);
}
else
{
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('SELECT distinct * FROM vs_customer c
INNER JOIN vs_customer_group g ON g.id_customer = c.id_customer
INNER JOIN vs_group_lang l on l.id_group = g.id_group WHERE id_lang = 2 '
);

$num_rows = mysql_num_rows($result);
}

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...