1

I am trying to design a query with OR and NAND operation, Can some one let me know Is the query is correct:

Question 1: Is this query correct?

select * from country where country_code='AL' UNION (SELECT * from country
WHERE country_id NOT IN
       (SELECT country_id from country
              where country_name='Land islands'
        UNION
        SELECT country_id from country
               where country_code='AX'
        ));

I created the query with the help of my another question of mine;

Ref:

Question 2:

I want to use 5 query with AND,NOT,NAND,OR operation in between them. I created up to 3 (Check my Question 1) I need help for creating 5 Select Queries.

My Application is search application where the user can search the database with i5 query block in between each block we have operations like below I mentioned. But the operations are carried out in one single table.

Explanation:

I want to select records inside a table and I will use many operations in between the table. MAX 5 operation in side a query.

Query 1,Query 2, Query 3, Query 4, Query 5.

Cases:

(Query 1 AND Query 2 OR  Query 3 NAND Query 4 NOT Query 5)
(Query 1 OR Query 2 OR  Query 3 OR Query 4 OR Query 5)
(Query 1 AND Query 2 AND Query 3 AND Query 4 AND Query 5)
(Query 1 NOT Query 2 NOT Query 3 NOT Query 4 NOT Query 5)

etc.. The operation in between the query Block may vary... I will write my PHP script according to that but I want to perform the Logic in such a way that I can join each logic..

Now I need an help

Community
  • 1
  • 1
DonOfDen
  • 3,968
  • 11
  • 62
  • 112
  • 1
    In your 5th query what you want to select. – Rahul Jun 05 '13 at 13:07
  • Whether or not the query is correct depends on what you are trying to achieve. Based on your question, it's not clear. You could be looking for certain records or you could be trying to use specific keywords. – Dan Bracuk Jun 05 '13 at 13:17
  • @TomPHP . . . Your revised second question does not make sense. Queries return results sets which are combined using operations like `UNION ALL` and `INTERSECT` and `MINUS`. Conditional clauses (such as in the answers to your question) are connected using `AND` and `OR` and perhaps `NOT`. – Gordon Linoff Jun 05 '13 at 14:08
  • Sorry @GordonLinoff I am not able to understand. I just edited my Q2 for more information. My situation is in Cases.. and many more.. but the operations will only be AND, OR, NAND NOT. I am not very experienced with UNION ALL , INTERSECT, MINUS. – DonOfDen Jun 05 '13 at 14:16
  • I am matching specific records with that match I want to pull the entire row. – DonOfDen Jun 05 '13 at 14:26

2 Answers2

2

Why you are using the UNION in between same Table. You may easily complete this query by using operators like

Select * from country where country_code='AL' or (country_name <> 'Land islands' and country_code <> 'AX');

Hope it works for you.

Rahul
  • 5,603
  • 6
  • 34
  • 57
  • Can you provide me a reference link to let me understand the use of "<>" operator in my Query.. CAn you give me any example or idea for using 5 query in between them I may use OR, AND, NAND, NOT. If its OR,AND then I can do it but this NAND, NOT operation confuses.. – DonOfDen Jun 05 '13 at 13:53
  • 1
    This is syntax `not (columnname1 and columnname2)` for `"NAND"` and `NOT IN` for your `NOT`. Now apply your logic. – Rahul Jun 06 '13 at 03:48
  • 1
    `<>` is `not equal operator` in mysql dear.http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html – Rahul Jun 06 '13 at 03:50
  • What about the cases.. can I have any syntax for that.. using multiple queries – DonOfDen Jun 06 '13 at 04:47
  • i really don't understand that why u r using `union`, try to fix that problem by using `operators`.. – Rahul Jun 06 '13 at 05:11
2

The simplest way to write your first query:

select *
from country
where country_code='AL' and country_name not in ('Land islands', 'AX')

This works under the assumption that country_id is unique in the country table -- a reasonable assumption based on the naming. If this is not the case, the query would be a bit more complicated.

Your original query requires multiple scans of the table, an anti-join for the NOT IN with a subquery, and aggregation to remove duplicates (it has union instead of union all).

The revised version just scans the table, keeping the rows that match the where clause.

Based on your first query, you can do everything you want just by using AND and OR (and perhaps IN and NOT IN) in a where clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786