Results 1 to 4 of 4

Thread: MySql to show records which is not a value

  1. #1
    Join Date
    May 2008
    Posts
    22

    MySql to show records which is not a value

    Hi,

    I am doing some query to MySQL database. I want something to find out the output records that have values such as say a one digit number. Like '5' etc.
    Is it possible? I dont know much about MySQL. Want MySQL to show result not having specific value.

    Thanks in advance.
    Grundy

  2. #2
    Join Date
    Apr 2008
    Posts
    51

    Re: MySql to show records which is not a value

    USE EXISTS and NOT EXISTS

    If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. For example:

    Code:
    SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
    an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.
    For the preceding example, if t2 contains any rows, even rows with nothing but NULL values, the EXISTS condition is TRUE. This is actually an unlikely example because a [NOT] EXISTS subquery almost always contains correlations.

    More Examples

    What kind of store is present in one or more cities?
    Code:
    SELECT DISTINCT store_type FROM stores
      WHERE EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type);
    What kind of store is present in no cities?

    Code:
    SELECT DISTINCT store_type FROM stores
      WHERE NOT EXISTS (SELECT * FROM cities_stores
                        WHERE cities_stores.store_type = stores.store_type);

  3. #3
    Join Date
    Oct 2008
    Posts
    79

    Re: MySql to show records which is not a value

    The most Important thing is the data type of the value you want to exclude.
    You need to use wild card characters too in the query.

    Suppose its char then use ... where attribute != 'char' or ... where attribute != '%word%'
    For null ...where attribute is NOT NULL
    For specific chars USE .. where Attribute NOT IN ('k', 'j', ...)

    I hope this is what you were looking for.

  4. #4
    Join Date
    May 2008
    Posts
    43

    Re: MySql to show records which is not a value

    Query to use Mysql not like :

    Code:
    mysql> select * from employee where empname not like 'Grundy';
    The Query below is used to return those records which are not specified in Where Clause

    The LIKE and NOT LIKE have two search helper symobls. The underscore _ character that looks for one character and the percentage % character that looks for zero or more characters.

    Code:
    $sql = mysql_query("SELECT * FROM table_name WHERE columnname LIKE value%");
    
    while ($row = mysql_fetch_row($sql)) {
    echo "$row[0] $row[1] $row[2] <br />";
    }
    The query will only pick out the rows that provide a TRUE result according to the WHERE equation. The equation will equal the LIKE VALUE plus some possible extra characters afterwards.

    Code:
    $sql = mysql_query("SELECT * FROM address_book WHERE last_name LIKE 'Stan%'");
    
    while ($row = mysql_fetch_row($sql)) {
    echo "$row[0] $row[1] $row[2] <br />";
    }

Similar Threads

  1. Importing large quantities of records in MySQL DB
    By Allison in forum Software Development
    Replies: 3
    Last Post: 03-12-2010, 04:24 AM
  2. Replies: 3
    Last Post: 19-07-2010, 04:23 PM
  3. PHP mysql select unique records from one table
    By Captain Carrot in forum Software Development
    Replies: 6
    Last Post: 13-05-2010, 12:18 PM
  4. Replies: 3
    Last Post: 07-11-2009, 09:36 PM
  5. How to Extract records from Mysql database using JTable
    By StudyBoy in forum Software Development
    Replies: 3
    Last Post: 08-04-2009, 06:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,625,227.57812 seconds with 17 queries