Thread: SELECT issues

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0

    Exclamation 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

    Also.....


    An even more perfect situation would be if I could combine the two SELECTS into one.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    here ya go, one query --
    Code:
    SELECT product_attribute.supplier_reference
         , product.price
         , product.wholesale_price
      FROM product_attribute
    INNER
      JOIN product
        ON product.id_product = product_attribute.id_product
    UNION -- removes duplicates    
    SELECT product_supplier.product_supplier_reference
         , product.price
         , product.wholesale_price
      FROM product_supplier
    INNER
      JOIN product
        ON product.id_product = product_supplier.id_product
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    YOU ROCK!

    Work perfectly and I learned something new.

    Thanks a million.

    Comments on this post

    • cafelatte agrees : Of course, we ALREADY knew that Rudy rocks :-)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by rseigel
    ... and I learned something new.
    hopefully, this new thing you learned was how to write explicit JOIN syntax rather than the deprecated comma list style with the join condition in the WHERE clause

    but i'm curious why you bothered to also post this question on dbforums, ~after~ i posted my solution here

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    hopefully, this new thing you learned was how to write explicit JOIN syntax rather than the deprecated comma list style with the join condition in the WHERE clause

    but i'm curious why you bothered to also post this question on dbforums, ~after~ i posted my solution

    Point taken.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    I actually posted them both at the same time - give or take.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by rseigel
    I actually posted them both at the same time - give or take.
    if i notice it sooner (which i did not, this time), i will usually post a link from one forum into the other, even if no replies have been made yet

    i hate seeing people waste their time, as actually happened in this case, re-creating a solution that's already been posted, or worse, barking up the wrong tree because further clarification was elicited in the other forum that they aren't aware of

    the only circumstance under which i find cross-posting acceptable is when a goodly amount of time has elapsed on one forum with no replies, then it's okay to post the same question into a different forum

    not everyone agrees with this sentiment, though

    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo