SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL subquery problems

    I recently transferred a friends data over to MySQL from Access and now I am getting this error :

    Error Executing Database Query.
    Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select distinct resourceid from tracking where bandid = 1567)'

    for this section of code:


    PHP Code:
    <cfquery name="testing" datasource="submissions"
            
    SELECT      
            
    FROM           resources 
            WHERE       ResourceID not in 
    (select distinct ResourceID from Tracking where BandID =#BandID#) 
            
    AND         ResourceID not in (select distinct ResourceID from Remove where BandID #BandID#) 
            
    AND            (MusicWriter '1'
            AND            (
    RegionCity is null
            AND            (
    Genre1 '#FORM.BandMasterGenre1#' OR Genre2 '#FORM.BandMasterGenre1#'  OR Genre3 '#FORM.BandMasterGenre1#'  OR Genre4 '#FORM.BandMasterGenre1#'  OR Genre5 '#FORM.BandMasterGenre1#' 
            
    OR Genre1 '#FORM.BandMasterGenre2#' OR Genre2 '#FORM.BandMasterGenre2#'  OR Genre3 '#FORM.BandMasterGenre2#'  OR Genre4 '#FORM.BandMasterGenre2#'  OR Genre5 '#FORM.BandMasterGenre2#' 
            
    OR Genre1 '#FORM.BandMasterGenre3#' OR Genre2 '#FORM.BandMasterGenre3#'  OR Genre3 '#FORM.BandMasterGenre3#'  OR Genre4 '#FORM.BandMasterGenre3#'  OR Genre5 '#FORM.BandMasterGenre3#' 
            
    OR Genre1 '#FORM.BandMasterGenre4#' OR Genre2 '#FORM.BandMasterGenre4#'  OR Genre3 '#FORM.BandMasterGenre4#'  OR Genre4 '#FORM.BandMasterGenre4#'  OR Genre5 '#FORM.BandMasterGenre4#'
            
    ORDER BY       resources.companyname
    </
    cfquery
    If someone could point out the differences for me that would be great. Specifically I need to know how to do this without subqueries as this version of MySQL cannot handle them. I could probably extrapolate from this to the rest of the site's code.
    his server is running MySQL 4.0.16 on windows 2003 IIs with coldfusion 6.1 MX.

    Thanks for your help in advance!

    V

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select Resources.*       
      from Resources 
    left outer 
      join Tracking 
        on Resources.ResourceID 
         = Tracking.ResourceID 
       and Tracking.BandID =#BandID#   
    left outer 
      join Remove 
        on Resources.ResourceID 
         = Remove.ResourceID 
       and Remove.BandID =#BandID#   
    where Tracking.ResourceID is null 
       and Remove.ResourceID is null
    if you still get no results, post a few rows of sample data from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    NYC
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Works

    Thanks that works great!


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
  •