Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Unhappy Unanswered: a simple sub query not working

    hi have 2 tables

    a)category
    +------------------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------------------+--------------+------+-----+---------+----------------+
    | categoryId | int(11) | | PRI | NULL | auto_increment |
    | categoryTitleEnglish | varchar(100) | YES | | NULL | |
    | categoryTitleFrench | varchar(100) | YES | | NULL | |
    | categoryDetailsEnglish | mediumtext | YES | | NULL | |
    | categoryDetailsFrench | mediumtext | YES | | NULL | |
    | superCategoryId | int(11) | YES | | NULL | |
    | subCategoriesId | mediumtext | YES | | NULL | |
    | imageURL | mediumtext | YES | | NULL | |
    +------------------------+--------------+------+-----+---------+----------------

    b)items table
    +---------------------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------------------+--------------+------+-----+---------+----------------+
    | itemId | int(11) | | PRI | NULL | auto_increment |
    | modelNumber | varchar(20) | YES | | NULL | |
    | itemNameEnglish | varchar(100) | YES | | NULL | |
    | itemNameFrench | varchar(100) | YES | | NULL | |
    | itemPriceCAD | varchar(10) | YES | | NULL | |
    | itemDescriptionEnglish | mediumtext | YES | | NULL | |
    | itemDescriptionFrench | mediumtext | YES | | NULL | |
    | imageURL | mediumtext | YES | | NULL | |
    | stocksAvailable | char(1) | YES | | NULL | |
    | categoryId | int(11) | | | 0 | |
    | imageAvailableFinishesURL | mediumtext | YES | | NULL | |
    +---------------------------+--------------+------+-----+---------+----------------+

    I WANT TO SELECT CATEGORIES FOR WHICH THERE are no ITEMS..

    but the following query is not working.. please help

    select categoryId from category where categoryId NOT IN (select categoryId from items)

    I GET the following error
    ERROR 1064: You have an error in your SQL syntax near 'select categoryId from items)' at line 1

    thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    what version of mysql? Can't do subquery prior to 4.1

  3. #3
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22
    mysql Ver 11.18 Distrib 3.23.54, for redhat-linux-gnu (i386)
    so i will update my mysql

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    categories for which there are no items --
    Code:
    select c.categoryId
         , c.categoryTitleEnglish
         , c.categoryTitleFrench
         , c.categoryDetailsEnglish
         , c.categoryDetailsFrench
         , c.imageURL 
      from category as c
    left outer
      join items as i
        on c.categoryId 
         = i.categoryId 
     where i.categoryId is null
    works prior to 4.1 too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Gandhinagar India
    Posts
    22

    Thumbs up

    thanks..that was perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •