kesaven Posted March 28, 2014 Share Posted March 28, 2014 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 cINNER JOIN vs_customer_group g ON g.id_customer = c.id_customerINNER JOIN vs_group_lang l on l.id_group = g.id_groupWHERE 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? Link to comment Share on other sites More sharing options...
Paul C Posted March 28, 2014 Share Posted March 28, 2014 (edited) 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 March 28, 2014 by Paul C (see edit history) Link to comment Share on other sites More sharing options...
kesaven Posted March 31, 2014 Author Share Posted March 31, 2014 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 More sharing options...
kesaven Posted March 31, 2014 Author Share Posted March 31, 2014 Hello I have eventually found a solution to my problem: $exsql = 'SELECT distinct c.id_customerFROM vs_customer cLEFT OUTER JOIN vs_customer_group g ON g.id_customer = c.id_customerINNER JOIN vs_group_lang l on l.id_group = g.id_groupWHERE l.id_lang = 2AND 1 =1 ';$idds = explode(",", $_GET['ids']);foreach($idds as $idd){$exsql = $exsql.' AND EXISTS (SELECT *FROM vs_customerLEFT OUTER JOIN vs_customer_group g ON g.id_customer = vs_customer.id_customerINNER JOIN vs_group_lang l on l.id_group = g.id_groupWHERE 1 =1AND 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 cINNER JOIN vs_customer_group g ON g.id_customer = c.id_customerINNER 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now