SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL not error but not showing exact records

    I hope this time i posted in right forum but still it is somewhat savvy with Coldfusion so i hope you guys don't mind:

    Here is my query:

    SELECT *
    FROM table1
    LEFT JOIN categories ON table1.catID = categories.catID
    WHERE
    categories.parentid = #url.catID#
    or
    categories.catID = #url.catID#
    AND Allow = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y">
    AND Approve = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y">
    <cfif isdefined('url.t') AND url.t eq 'upda'>
    AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
    <cfelse>
    AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="No">
    </cfif>

    Ok Now either the isTut is yes or No, it returens all records .

    it does not filter the records based on istut [yes/no]

    don't know what is going on with this.

    I tried assembing it many ways but no luck yet

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you have this --

    ... WHERE x OR y AND p AND q AND r

    because ANDs take precedence over ORs, this is evaluated as --

    ... WHERE x OR ( y AND p AND q AND r )

    it's pretty obvious that what you want is this --

    ... WHERE ( x OR y ) AND p AND q AND r

    tip: when mixing ANDs and ORs, always use parentheses to ensure you get the logic you want

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    by the way, if you write a LEFT OUTER JOIN and then use WHERE conditions involving a column in the right table, you are in effect getting an inner join, so you might as well write INNER JOIN instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Okay Thanks For the quick reply///

    I just tried the code and it displays an error:

    Error Executing Database Query.
    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 '(AND Allow = 'Y' AND Approve = 'Y') AND isTut' at line 8

    i tried something like this:

    SELECT *
    FROM table1
    LEFT JOIN categories ON table1.catID = categories.catID
    WHERE
    categories.parentid = #url.catID#
    or
    categories.catID = #url.catID#
    (AND Allow = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y">
    AND Approve = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y">
    <cfif isdefined('url.t') AND url.t eq 'upda'>
    AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
    <cfelse>
    AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="No">)
    </cfif>

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you had the parentheses in the wrong place
    Code:
    SELECT * 
      FROM table1 
    INNER
      JOIN categories 
        ON categories.catID = table1.catID
     WHERE (
           categories.parentid = #url.catID#
        OR categories.catID = #url.catID#
           )
       AND Allow = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y"> 
       AND Approve = <cfqueryparam cfsqltype="cf_sql_varchar" value="Y"> 
    <cfif isdefined('url.t') AND url.t eq 'upda'>
       AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
    <cfelse>
       AND isTut = <cfqueryparam cfsqltype="cf_sql_varchar" value="No">
    </cfif>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •