Professional Web Applications Themes

Use of OR and % in a SELECT - ASP Database

Hi All, I think this problem might be my SQL, but I am not too sure at this point. I have the following tables: The pages stores string data about a bunch of url's. pages: - title (pk) - pageUrl - notes The subjects tables stores a list of string keywords I wish to relate to the url's. subjects: - subject (pk) This tables links subjects with pages. pageSubjects - title (fk) - subject (fk) Here is a sample query that is bugging me: select distinct pages.title, pages.pageUrl, pages.notes from pages, pageSubjects where pageSubjects.subject like '%jini%' or pageSubjects.title like '%jini%' ...

  1. #1

    Default Use of OR and % in a SELECT

    Hi All,

    I think this problem might be my SQL, but I am not too sure at this point.

    I have the following tables:

    The pages stores string data about a bunch of url's.
    pages:
    - title (pk)
    - pageUrl
    - notes

    The subjects tables stores a list of string keywords I wish to relate to the
    url's.
    subjects:
    - subject (pk)

    This tables links subjects with pages.
    pageSubjects
    - title (fk)
    - subject (fk)

    Here is a sample query that is bugging me:

    select distinct pages.title, pages.pageUrl, pages.notes
    from pages, pageSubjects
    where pageSubjects.subject like '%jini%'
    or pageSubjects.title like '%jini%'
    and pages.title = pageSubjects.title
    order by pages.title;

    There is one record that has "jini" in its title field - but when I execute
    this in Access I get no results.. The strangest part is that when I execute
    it through my ASP page it returns *the whole table*!!

    This query correctly returns the single row:

    select distinct pages.title, pages.pageUrl, pages.notes
    from pages, pageSubjects
    where pageSubjects.subject like '%jini%'
    and pages.title = pageSubjects.title
    order by pages.title

    Any advice would be most appreciated!

    Rob
    :)


    Robert Guest

  2. #2

    Default Re: Use of OR and % in a SELECT

    try placing brackets round the or part like this

    select distinct pages.title, pages.pageUrl, pages.notes
    from pages, pageSubjects
    where (pageSubjects.subject like '%jini%'
    or pageSubjects.title like '%jini%')
    and pages.title = pageSubjects.title
    order by pages.title;

    I've had problems like this before and doing this sorted it

    HTH

    "Robert Mark Bram" <none> wrote in message
    news:414e4f2d$0$20125$optusnet.com.au... 


    Steven Guest

  3. #3

    Default Re: Use of OR and % in a SELECT

    As Steven's reply indicates, you need parens around the OR clause. For
    future reference, this is because of "operator precedence". AND has higher
    precedence than OR so expressions containing AND are evaluated first. Parens
    change the order of evaluation.

    Your original expression would be evaluated as

    where pageSubjects.subject like '%jini%' or
    ( pageSubjects.title like '%jini%' and pages.title = pageSubjects.title )

    This prevents the clause you are using to join the 2 tables (pages.title =
    pageSubjects.title) from selecting only matching records so you get the
    whole pages table back for each selected row in pageSubjects. The reason
    that you get nothing back in the Access user interface is that Access uses
    "*" instead of "%" for the multi-character wildcard character.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Robert Mark Bram" <none> wrote in message
    news:414e4f2d$0$20125$optusnet.com.au... 
    the 
    execute 
    execute 


    Mark Guest

  4. #4

    Default Re: Use of OR and % in a SELECT

    Thank you Mark and Steven - my gratitude is endless!
    I went to bed frustrated and woke up fresh to find your replies which fixed
    up my page. :)

    Rob
    :)


    Robert Guest

Similar Threads

  1. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139