SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Need help refining query with a join to retrieve specific records

    I looked for help with this in late December but perhaps was not framing the question correctly.

    I've used this query to pull a list of products from a Zencart database:

    Code:
    $query = "
    SELECT * FROM products 
    LEFT JOIN products_description
    ON 
    products.products_id = products_description.products_id
    GROUP BY products.products_id
    order by master_categories_id ASC
    ";
    I need to somehow refine my query to select only products with a specific master_categories_id, in order to display items only from a specific category.

    I tried this:
    Code:
    $query = "
    SELECT * FROM products where master_categories_id = 3
    UNION
    SELECT * FROM products 
    LEFT JOIN products_description
    ON 
    products.products_id = products_description.products_id
    GROUP BY products.products_id
    ";
    But I keep getting the following error: The used SELECT statements have a different number of columns. Is there a different way to construct this query that would work better (ie some sort of workaround for the differing number of columns)?

    Thanks for your help.

  2. #2
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ended up with this and it seems to be working.

    Code:
    $query = "SELECT products.products_id,products.products_image,products.products_price,products.master_categories_id,products_description.products_description
    FROM products LEFT JOIN products_description ON
    products.products_id = products_description.products_id 
    where master_categories_id = 18
    order by master_categories_id ASC"
    ;

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you don't actually need the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, you're right. Thanks Rudy.


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
  •