Results 1 to 2 of 2

Thread: NOT IN sytnax

  1. #1
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Unanswered: NOT IN sytnax

    I have a SQL that I can't get to work and I think I have the syntax incorrect. Here it is.

    SELECT DISTINCT manufacturers.mfrid, manufacturers.mfrname FROM manufacturers, models, oemmemory, crossref, pdppart, exclude WHERE manufacturers.mfrid = models.mfrid AND models.modelid = oemmemory.modelid AND crossref.crossrefid = oemmemory.crossrefid AND crossref.crossrefid = pdppart.crossrefid AND models.modelid not in(select exclude.modelid from exclude) AND models.catid = ? ORDER BY manufacturers.mfrname;

    Basically it tells me I have an error near the select exclude.modelid from exclude.

    What I'm trying to do is to get a list of models but not ones that are listed in the exclude table.

    Thanks for any help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    subqueries not supported before version 4.1

    try this --
    Code:
    select distinct 
           manufacturers.mfrid
         , manufacturers.mfrname 
      from manufacturers
    inner
      join models
        on manufacturers.mfrid 
         = models.mfrid 
    inner
      join oemmemory
        on models.modelid 
         = oemmemory.modelid 
    inner
      join crossref
        on oemmemory.crossrefid  
         = crossref.crossrefid 
    inner
      join pdppart
        on crossref.crossrefid 
         = pdppart.crossrefid
    left outer
      join exclude 
        on models.modelid
         = exclude.modelid
     where models.catid = 937 
       and exclude.modelid is null
    order 
        by manufacturers.mfrname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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