SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast appy's Avatar
    Join Date
    Feb 2002
    Location
    UK
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT query help

    Hi All,

    I've been asked by a friend to help with some design for a site built a couple of years ago and I've come across a couple of data errors that MySQL should be able to sort out but I'm afraid that my MySQL knowledge is pretty limited.

    The situation is a product search and uses the query below...

    Code:
    SELECT products_description.products_id, products_description.language_id, products_to_categories.categories_id
    FROM products_description
    INNER  JOIN products_to_categories ON products_description.products_id = products_to_categories.products_id
    WHERE products_description.products_name LIKE  '%searchterm%'
    OR products_description.products_description LIKE  '%searchterm%'
    However, products are being returned that are not supposed to be 'active'.

    There's a table called
    Code:
    product
    which has a column called
    Code:
    products_status
    which can be a 1 or 0.

    So what I need to do is add to the original query
    Code:
    AND product.products_status = 1
    but can't quite work out the way to do it.

    Any help would be great.

    Thanks,

    Pete

  2. #2
    doing my best to help c2uk's Avatar
    Join Date
    May 2005
    Location
    Cardiff
    Posts
    1,832
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as shown in another thread, my SQL skills are also limited, but it looks to me like we need another JOIN between the products table and the products_description table, likely again on the products_id (I assume this field is available in the products table?). Then
    Code:
    WHERE product_status = 1 AND (products_description.products_name LIKE  '%searchterm%'
    OR products_description.products_description LIKE  '%searchterm%')
    Let's see what others are saying
    Dan G
    Marketing Strategist & Consultant

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select products_description.products_id, 
           products_description.language_id, 
           products_to_categories.categories_id
      from products_description
      join products_to_categories 
        on products_description.products_id = products_to_categories.products_id
      join products 
        on products.products_id = products_description.products_id
       and products.products_status = 1
     where products_description.products_name like '%searchterm%'
        or products_description.products_description like '%searchterm%'
    You did not mention how to join the products table, so you may have to change this line

    Code:
     on products.products_id = products_description.products_id
    depending on your table definitions.

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To do what you want to do it must be possible to join the product table to one of the other two tables in the Select statement. In the same way as the products_to_categories table is joined to the products_description. That requires that you identify the field that the two tables have in common.

    I would speculate that your table product has a field id and that this is the same as the products_id field on the products_description table.

    so you need a

    Code:
    LEFT JOIN product ON product.id = products_description.products_id
    and then you can extend you WHERE clause

    Code:
    WHERE (products_description.products_name LIKE  '%searchterm%'
    OR products_description.products_description LIKE  '%searchterm%')
    AND product.products_status = 1

  5. #5
    SitePoint Enthusiast appy's Avatar
    Join Date
    Feb 2002
    Location
    UK
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks a lot for the quick responses. An extra join and where clause has done the trick perfectly.

    Pete

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    philip, your LEFT JOIN should be an INNER JOIN

    it is extremely unlikely that there will be a row in products_description with a product id that isn't in the products table
    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
  •