CODEWITHSUNDEEP
mysqlsqllogiclogical-operators
DonOfDen
DonOfDen

Reputation: 4088

SELECT .. SELECT in Mysql

I am trying to write a query for mysql (Search Module) where I am facing difficulties because of the select operation to be carried out inside same table.

My Query:

(SELECT * FROM user WHERE `user_name` like '%TOM%' OR `user_name` like '%AN%' 
and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59')
    OR
(SELECT * FROM user WHERE `user_name` like '%PHP%' OR `user_name` like '%BA%' 
and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59')
    AND
(SELECT * FROM user WHERE `user_name` like '%SUN%' OR `user_name` like '%MOON%' 
and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59') 
    NAND
(SELECT * FROM user WHERE `user_name` like '%RAJ%' OR `user_name` like '%MUTH%' 
and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59') 
    NOR
 (SELECT * FROM user WHERE `user_name` like '%BAG%' OR `user_name` like '%LAP%'
 and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')

The above mentioned query is my scenario.. Where this cannot be changed, I am trying to write a Query for this.

Note:

  1. The above query execute in a single table.
  2. OR, NAND, NOR, AND operation can be inter-changed with according to the situation.
  3. It depends with the user, how he needs to search.

I tried many ways to achieve the logic but I am facing issues in all phases.

Check my try: MySQL IN BETWEEN with no condition Doctrine Query from Mysql Select Query with OR and NAND

Upvotes: 0

Views: 163

Answers (1)

LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

NAND and NOR operations can be done as follows in SQL: link

Just edit the following query as you need it.

SELECT * FROM user WHERE 
(`user_name` like '%TOM%' OR `user_name` like '%AN%' and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59') OR
NOT ( --NOR
   (`user_name` like '%PHP%' OR `user_name` like '%BA%' and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59') OR
   (`user_name` like '%SUN%' OR `user_name` like '%MOON%' and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
) OR
NOT ( --NAND
   (`user_name` like '%RAJ%' OR `user_name` like '%MUTH%' and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59') AND
   (`user_name` like '%BAG%' OR `user_name` like '%LAP%' and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
)

In the above example all records are returned where the condition between the first parenthesises is true OR (2nd condition NOR 3rd condition) OR (4th condition NAND 5th condition).

NOTE: condition refers to the clauses between parenthesises.

Upvotes: 1

Related Questions