Question 20916 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get one on one record using mysql query? I used GROUP BY also but didn't get result

I’m stuck as I need only one on one records to be returned by my query.

My query:

    SELECT emp_code
    FROM Test_app_emp_assign_to_store_
    WHERE `store_code` IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
    GROUP BY store_code, emp_code
    HAVING count(store_code) = '1';

Only one on one relation data required

Need records only one on one

Tags:

2

Answers


  1. 0

    use sample :

    SELECT *
FROM Test_app_emp_assign_to_store_
WHERE 
`store_code` IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
AND `store_code` NOT IN (
    SELECT `store_code` FROM Test_app_emp_assign_to_store_ 
    GROUP BY store_code
    HAVING count(store_code) > 1  
) 
AND `emp_code` NOT IN (
    SELECT `emp_code` FROM Test_app_emp_assign_to_store_ 
    GROUP BY emp_code
    HAVING count(emp_code) > 1  
) 
ORDER BY `Test_app_emp_assign_to_store_`.`id` ASC;

    sample :

    id      emp_code    emp_type    store_code  created_at  
1029    AB0319  2   AUMT0289    2023-02-03 16:34:19 
1030    AB0318  2   AUGT0011    2023-02-03 16:34:19 
1031    AB0359  2   AUMT0270    2023-02-03 16:34:19 
1032    AB0364  2   AUGT0039    2023-02-03 16:34:19 
1033    AB0374  2   AUGT0176    2023-02-03 16:34:19

    Create Table:

    CREATE TABLE IF NOT EXISTS `test_app_emp_assign_to_store_` (
  `id` int(11) NOT NULL,
  `emp_code` varchar(255) NOT NULL,
  `emp_type` int(11) NOT NULL,
  `store_code` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Insert Data :

    INSERT INTO `test_app_emp_assign_to_store_` (`id`, `emp_code`, `emp_type`, `store_code`, `created_at`) VALUES
(1027, 'AB0274', 2, 'AUGT0052', '2023-02-03 16:34:19'),
(1028, 'AB0289', 2, 'AUGT0052', '2023-02-03 16:34:19'),
(1029, 'AB0319', 2, 'AUMT0289', '2023-02-03 16:34:19'),
(1030, 'AB0318', 2, 'AUGT0011', '2023-02-03 16:34:19'),
(1031, 'AB0359', 2, 'AUMT0270', '2023-02-03 16:34:19'),
(1032, 'AB0364', 2, 'AUGT0039', '2023-02-03 16:34:19'),
(1033, 'AB0374', 2, 'AUGT0176', '2023-02-03 16:34:19'),
(1034, 'AB0452', 2, 'AUGT3008', '2023-02-03 16:34:19'),
(1035, 'AB0452', 2, 'AUMT0333', '2023-02-03 16:34:19'),
(1036, 'AB0452', 2, 'AUGT0382', '2023-02-03 16:34:19');
    Login or Signup to reply.
  2. 0

    You can do it using GROUP BY HAVING count(1) twice:

      with cte as (
  SELECT emp_code
  FROM Test_app_emp_assign_to_store_
  WHERE `store_code` IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
  AND store_code not  in (
    SELECT store_code
    FROM Test_app_emp_assign_to_store_
    GROUP BY store_code
    HAVING count(1) > '1'
  )
  GROUP BY emp_code
  HAVING count(1) = '1'
  )
  select t.*
  from cte c
  inner join Test_app_emp_assign_to_store_ t on c.emp_code = t.emp_code;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search