SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: SQL question

  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL question

    I've pasted the code below. Everything is fine except the WHERE part. I have a search form with two checks, col22 and col11(which are a22 and a11 on the form). Obviously these are either checked or unchecked. The form only includes one other text box. I trying to get the query to take the input from the text box a99, to search all the other columns in the WHERE clause with wildcards. Maybe I have the bracketing wrong for it to search properly. I'm not sure. But I can't get it find any records even though they exist.

    SELECT tbl3.col1, tbl3.col2, tbl3.col3, tbl3.col4, tbl3.col5, tbl3.col6, tbl3.col7, tbl3.col17, substring(tbl3.col17
    FROM 1 for 8)||'-'||substring(tbl3.col17 from 9 for 3) as col17formatted, tbl3.col18, substring(tbl3.col18 from 1 for 2)||'-'||substring(tbl3.col18 from 3 for 7) as col18formatted, tbl3.col19, tbl3.col22 , tbl4view.col6 as col6b, '('||substring(tbl4view.col6 from 1 for 3)||') '|| substring(tbl4view.col6 from 4 for 3)||'-'||substring(tbl4view.col6 from 7 for 4) as col6bformatted, tbl4view.col8 as col8b, '('||substring(tbl4view.col8 from 1 for 3)||') '|| substring(tbl4view.col8 from 4 for 3)||'-'||substring(tbl4view.col8 from 7 for 4) as col8bformatted, tbl4view.col9 as col9b, '('||substring(tbl4view.col9 from 1 for 3)||') '|| substring(tbl4view.col9 from 4 for 3)||'-'||substring(tbl4view.col9 from 7 for 4) as col9bformatted, tbl4view.col2 as col2b, tbl4view.col4 as col4b FROM tbl3 LEFT JOIN tbl4view ON tbl3.col1 = tbl4view.col13
    WHERE (tbl3.col22='a22' AND tbl3.col11='a11') and (tbl3.col2 Like '%a99%' or tbl3.col3 Like '%a99%' or tbl3.col7 Like '%a99%' or tbl3.col4 Like '%a99%' or tbl3.col5 Like '%a99%' or tbl3.col17 Like '%a99%' or tbl3.col18 Like '%a99%'or tbl3.col19 Like '%a99%')
    ORDER BY tbl3.col2 ASC

  2. #2
    SitePoint Zealot Pie's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can I reccomend you seperate your code neatly. Its a lot easier to work out the problem when its properly ident'd, seperated etc.
    Zach Inglis
    Reality Art Studios Ltd
    ZachInglis.com Blog |
    Art / Code Chat & Tutorials | Work


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
  •