1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

rewriting a MS SQL query to work with mySQL

Discussion in 'MySQL' started by MySQLGEEK, Aug 29, 2006.

  1. MySQLGEEK

    MySQLGEEK New Member

    Joined:
    Aug 29, 2006
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    I have a SQL query that works fine in MS SQL, but I want to be able to use this query in a mySQL database. The problem is mySQL doesn't support this type of query. MySQL doesn't support sub queries.

    I've tried looking but can't seem to find out how I could perform this same thing in mysql a different way. I thougth I'd check to see if anyone might have a sugestion, or know of how to rewrite it to make it work.

    Here is the SQL:
    Code:
    SELECT categories.name AS category, 
                manufacturer.name AS manufacturer, 
                manufacturer.website AS website, 
                product.short_description, 
                product.part_number, 
                product.id,
                (    
                     SELECT MIN(price)
                     FROM seller_item
                     WHERE product_id = product.id
                ) AS price
    FROM product 
             INNER JOIN cat_product_connector 
                 ON product.id = cat_product_connector.product_id 
             INNER JOIN categories 
                 ON cat_product_connector.cat_id = categories.id 
             INNER JOIN manufacturer 
                 ON product.manufacturer_id = manufacturer.id
    WHERE categories.id = ? 
        AND product.status = 'active'
    ORDER BY categories.name
    My big problem is the sub select statement that I use to get the lowest price from the table that lists the multiple sellers caring that product.

    I don't see how I could do this with a table join because I have to get the minimum price for each product.

    I didn't want to make my application have to execute a select query for each product it gets from that query because of the overhead involved. I would have to open a second DB connection to issue the select statements finding the minimum price for each product as the program iterates through the main query listed above. Plus this query is going to be one of the most heavily executed ones, so the least overhead the better.
     
  2. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:

Share This Page