SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    where clause with LIKE

    Hi,

    I have a working query which uses the WHERE to match exactly with a given value.

    I have have now changed it to match LIKE and it returns two results where there is just one business and just one record for that business in the db.

    Is there anything in this query that would cause this to happen? It doesn't happen with other records, where there is one business and one address.
    I have checked the db and the business and its address are not entered twice but there are two businesses which match on the term 'malo'.

    Code mysql:
    SELECT 
          BD.business_id
        , BD.business
        , ADDR.name_no
        , ADDR.address_1
        , ADDR.address_2
        , ADDR.address_3
        , ADDR.town_or_townland
        , ADDR.city_or_county_name
        , ADDR.post_code
        , ADDR.country_name 
        FROM business_details BD
        INNER join address ADDR
        WHERE BD.business LIKE '%${business_name}%' 
        AND BD.business_id = ADDR.business_id
        AND ADDR.address_type = 'actual'

    bazz

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you show the rows in question?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your syntax is slightly wrong

    change this --
    Code:
    FROM business_details BD
    INNER join address ADDR
    WHERE BD.business LIKE '%${business_name}%' 
    AND BD.business_id = ADDR.business_id
    AND ADDR.address_type = 'actual'
    to this --
    Code:
      FROM business_details BD
    INNER 
      JOIN address ADDR
        ON ADDR.business_id = BD.business_id
       AND ADDR.address_type = 'actual'
     WHERE BD.business LIKE '%${business_name}%'
    if this still returns 2 rows, then either you have two businesses with one address each, or one business with two addresses
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry guys.

    I had built this script about a year ago and I forgot that I had a 'count' loop which I realise now, is pointless. (now that I have added sufficient records).

    Thanks for the syntax pointer Rudy. I had overlooked that when I changed the query from the old style (below) to how I do it now .

    Code:
    select
    col1, col2, col3
    from table 1 t1, table2 t2, table3 t3
    where blah = ?
    and blurb = ?
    Thanks Guelpdad as well. you prompted me to find the stupid loop.

    bazz


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
  •