What's wrong with this MySQL syntax?

Discussion in 'OT Technology' started by TheDarkHorizon, Nov 29, 2003.

  1. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    mysql> SHOW TABLES LIKE 'products';
    +--------------------------------+
    | Tables_in_database (products) |
    +--------------------------------+
    | products |
    +--------------------------------+
    1 row in set (0.00 sec)

    mysql> SHOW TABLES NOT LIKE 'products';
    ERROR 1064: You have an error in your SQL syntax near 'not like 'products'' at line 1
     
  2. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    Basically, I have three tables:

    One
    Two
    Three

    If I SHOW TABLES, it will display all three, but I do not want it to display the table named "Three".

    Is there a way I can SHOW TABLES so it only displays One and Two?

    I was trying SHOW TABLES NOT LIKE 'three', but it does not seem to work.
     
  3. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    Anyone? I was trying a workaround, but it eventually messed up because of that extra table that I do not want to display.
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    hmmm, sorry man... i couldn't find anything in the 5min that i looked. Are those the real table names? or can you give your tables that you want specifically a unique name... for instance a prefix or suffix that you can use in your like to just get those out?
     
  5. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    I fiddled with this. It looks like MySQL doesn't support the "not" option when using "SHOW".

    The only thing I can think of right now is to dump all the table names to an array and fiddle with the array or dump the results to a temp table and then issue your query. I think the array will give you better performance unless you have a crazy amount of tables.
     
  6. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    I'm thinking about prefixing them with something, but I am not sure which character I should use though.

    http://forums.devshed.com/archive/4/2003/9/4/85959 was one of the only websites I found with the exact match of "show tables not like". I found another website that show the syntax, and it showed NOT LIKE, but it didn't give any examples.
     
  7. TheDarkHorizon

    TheDarkHorizon \xC0\xFF\xEE

    Joined:
    Sep 26, 2002
    Messages:
    2,396
    Likes Received:
    0
    Location:
    San Francisco, CA
    I've tried that, but I have no idea what the problem is though (I'm assigning numbers to the tables). I'll try debugging it again later.
     

Share This Page