MySQL and Query to Satisfy on Same Column

MySQL query with multiple conditions on same column

Try this.

SELECT * 
FROM employees 
WHERE (coverage_thru_date >= TO_DATE('01-JAN-2017')
      AND plan_name ='Choice Health')
      OR
      (coverage_start_dt >= coverage_thru_dt
       AND plan_name='Waive Medical');

--------------REVISED BASED ON SUPPLIED QUERY--------------
SELECT peo.employee_number, 
       bpf.name plan_name, 
       typ.name plan_type, 
       ben.effective_start_date, 
       ben.effective_end_date, 
       ben.enrt_cvg_strt_dt, 
       ben.enrt_cvg_thru_dt 
FROM   apps.ben_prtt_enrt_rslt_f ben, 
       apps.ben_pl_f bpf, 
       apps.ben_pl_typ_f typ, 
       apps.per_all_people_f peo 
WHERE    ( ben.enrt_cvg_thru_dt >= '01-JAN-2017' 
          AND typ.name = 'Choice Health' ) 
       OR 
         ( ben.enrt_cvg_strt_dt >= ben.enrt_cvg_thru_dt 
          AND typ.name = 'Waive Health' ) 
       AND ben.person_id = peo.person_id 
       AND ben.pl_id = bpf.pl_id 
       AND typ.pl_typ_id = ben.pl_typ_id;

to write a select query which strictly satisfies a multiple condition on same column

You can do this using group by and having:

select ct.entityid
from companytagrel ct left join
     taginfo t
     on ct.tagid = t.tagid
where t.tag in ('own', 'rocking')
group by ct.entityid
having count(distinct t.tag) = 2;

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag is unique).

Or use joins:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

Sum different values of same type in same column in one SQL query

Use a CASE expression that changes 'Cash' to 'Bar', 'Credit-Card' to 'Kreditkarte' and 'Invoice' to 'Rechnung' and group by its result:

SELECT CASE payment
         WHEN 'Cash' THEN 'Bar'
         WHEN 'Credit-Card' THEN 'Kreditkarte'
         WHEN 'Invoice' THEN 'Rechnung'
         ELSE payment
       END AS payment, 
       COUNT(*) AS number 
FROM Test 
GROUP BY 1;

MySQL multiple WHERE IN on same column with AND operator

Use it like this

SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
FROM shopitem si
LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
HAVING
FIND_IN_SET('black', filter_options)
AND  FIND_IN_SET('41', filter_options)
AND FIND_IN_SET('man', filter_options)
GROUP BY si.id

For counting the number of si.id from the result you can use like this with subquery

SELECT COUNT(res.id) FROM
  (SELECT si.*, GROUP_CONCAT(so.filter_option_id) AS filter_options
   FROM shopitem si
   LEFT JOIN shopitem_option so ON(so.shopitem_id = si.id)
   HAVING
   FIND_IN_SET('black', filter_options)
   AND  FIND_IN_SET('41', filter_options)
   AND FIND_IN_SET('man', filter_options)
   GROUP BY si.id) 
 AS res

mysql multiple conditions on the same column

You can get the user_ids with aggregation if you set correctly the conditions in the HAVING clause:

SELECT user_id
FROM wp_push_notification_topics
GROUP BY user_id
HAVING SUM(topic_name = 'so') > 0 AND SUM(topic_name IN ('all', 'so2')) > 0;

If you want to restrict the conditions so that the user is not subscribed to any other than 'so', 'all' and 'so2' you can add to the HAVING clause:

AND  SUM(topic_name NOT IN ('so', 'all', 'so2')) = 0

If you want all the rows of the table:

SELECT *
FROM wp_push_notification_topics
WHERE user_id IN (
  SELECT user_id
  FROM wp_push_notification_topics
  GROUP BY user_id
  HAVING SUM(topic_name = 'so') > 0 AND SUM(topic_name IN ('all', 'so2')) > 0
);

---

Related Topics