Results 1 to 5 of 5

Thread: SELECT issues

  1. #1
    Join Date
    Oct 2012
    Posts
    17

    Unanswered: SELECT issues

    I have two SELECTS that grab the info that I need to create a csv file.

    $result = mysql_query("SELECT product_attribute.supplier_reference, product.price, product.wholesale_price
    FROM product_attribute, product
    WHERE product.id_product = product_attribute.id_product");

    $result = mysql_query("SELECT product_supplier.product_supplier_reference, product.price, product.wholesale_price
    FROM product_supplier, product
    WHERE product.id_product = product_supplier.id_product");

    Basically, I want the first one to run complete. It's fine as is.

    For the second one I want to eliminate any records where product.id_product was present in the first SELECT and add the rest to the csv.

    Any ideas?

    TIA!

    Ron

  2. #2
    Join Date
    Oct 2012
    Posts
    17
    An even more perfect situation would be if I could combine the two SELECTS into one.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If the price and wholesale price is identical for product supplier reference in both tables then have a look at using UNION as follows:

    SELECT product_attribute.supplier_reference, product.price, product.wholesale_price
    FROM product_attribute, product
    WHERE product.id_product = product_attribute.id_product
    UNION
    SELECT product_supplier.product_supplier_reference, product.price, product.wholesale_price
    FROM product_supplier, product
    WHERE product.id_product = product_supplier.id_product

    This will only display unique entries.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Oct 2012
    Posts
    17
    YOU ROCK!

    Work perfectly and I learned something new.

    Thanks a million.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea might be...

    Code:
    SELECT COALESCE(
              pa.supplier_reference
            , ps.product_supplier_reference
           ) AS supplier_reference
         , p.price
         , p.wholesale_price
     FROM  product           AS p
     LEFT  OUTER JOIN
           product_attribute AS pa
      ON   pa.id_product = p.id_product
     LEFT  OUTER JOIN
           product_supplier  AS ps
      ON   pa.id_product IS NULL
       AND ps.id_product = p.id_product
     WHERE
           pa.id_product IS NOT NULL
       OR  ps.id_product IS NOT NULL
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •