SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with SQL join query...

    This SQL statement returns all the correct rows from 2 tables:

    --------------
    SELECT DISTINCT Upload.NewsID, News.NewsID, News.UserID,Left(News.Title,255) As Title
    FROM News LEFT JOIN Upload ON News.NewsID=Upload.NewsID
    --------------

    But when I try to search for a known term in one of the fields no records are returned:

    --------------
    SELECT DISTINCT Upload.NewsID, News.NewsID, News.UserID,Left(News.Title,255) As Title
    FROM News LEFT JOIN Upload ON News.NewsID=Upload.NewsID

    WHERE Title LIKE 'ABCD'
    --------------

    Have tried using News.Title, '%ABCD%' but same problem.


    Any ideas?

    thanks,
    Mike

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    from best i can tell w/o actually seeing your DB, your query seems OK. try running the query w/o the where clause and examine the results to see if the record you're trying to retrieve is being returned to you. perhaps that'll be able to help you pinpoint the problem.

  3. #3
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks but tried that. Without the 'where' clause all records are present (inc the one with ABCD in the title)

    The only thing I can think of is that you can't use a contains search on a string that has been coverted from memo to string like as this one is in the SQL.

    Any other ideas?

  4. #4
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm. what database engine are you using?

    try adding the criteria in baby steps...
    see if adding WHERE Title LIKE '%' still works. if so then try WHERE Title LIKE 'A%'and then WHERE Title LIKE 'AB%' and so forth until you no longer get the expected results. that's about all i can think of without more knowledge of your database.

  5. #5
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    and youre absolutely shure that a there is a record in the upload table with the same id as the record in the news table??
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  6. #6
    SitePoint Enthusiast Trilliann's Avatar
    Join Date
    Apr 2002
    Location
    California
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think your problem is the "memo" type... What if you simply run this query:

    select NewsID from News where Title like '%ABCD%'

    Do you get any record?

    (is it MSSQL?)

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which table is the "Title" field in, and what are the datatypes? Can you supply table creation scripts and data? Have you tried taking out the "DISTINCT"?

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2002
    Location
    UK
    Posts
    146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i believe you mentioned in a previous post you were using access 2000.

    if so you need to use * instead of % so try this:
    Code:
    SELECT DISTINCT Upload.NewsID, News.NewsID, News.UserID,Left(News.Title,255) As Title 
    FROM News LEFT JOIN Upload ON News.NewsID=Upload.NewsID 
    WHERE News.Title LIKE "*ABCD*";
    of course if you're not using access just ignore me
    hope this helps


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
  •