Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: Filtering Result Rows

    I am a MySQL beginner.
    My table has the following columns:
    Code:
    Index | ProductID | CustID | GroupID | Price
    Every ProductID can have many prices for Groups and for individual CustIDs. This querry
    Code:
    SELECT ProductID , Price
       FROM prices
       WHERE GroupID  = '2' || ( CustID  = '12' && GroupID   = '0' )
    returns all the prices I need for "Group 2" + "Customer 12".
    I do not want the rows from " GroupID = '2' " when I also have another result row matching " CustID = '12' && GroupID = '0' " for the same ProductID.

    Can someone point me in the right direction?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ProductID
         , Price
      FROM prices
     WHERE CustID = 12 
       AND GroupID  = 0
    UNION ALL
    SELECT ProductID
         , Price
      FROM prices
     WHERE GroupID = 2
       AND NOT EXISTS
           ( SELECT 937
               FROM prices
              WHERE CustID = 12 
                AND GroupID  = 0 )
    also, a couple of pointers in the right direction:

    -- do not use those horrendous || and && abominations, but the standard SQL keywords OR and AND instead

    -- do not put quotes around numbers used with numeric columns

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    Thanks a lot r937,
    I tried your suggestion.
    One element seems to be missing in the " AND NOT EXISTS ( SELECT . . . " part.
    Code:
    SELECT ProductID
         , Price
      FROM prices
     WHERE CustID = 12 
       AND GroupID  = 0
    UNION ALL
    SELECT ProductID
         , Price
      FROM prices
     WHERE GroupID = 2
       AND NOT EXISTS
           ( SELECT Price
               FROM prices
              WHERE CustID = 12 
                AND GroupID  = 0
                AND ProductID = ?? )
    The ?? stands for the ProductID of the curently checked row. How do I get the current ProductID instead of the ??

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT ProductID
         , Price
      FROM prices
     WHERE CustID = 12 
       AND GroupID  = 0
    UNION ALL
    SELECT ProductID
         , Price
      FROM prices AS foo
     WHERE GroupID = 2
       AND NOT EXISTS
           ( SELECT 937
               FROM prices
              WHERE CustID = 12 
                AND GroupID  = 0  
                AND ProductID  = foo.ProductID )
    Last edited by r937; 07-20-09 at 08:55.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    Code:
    SELECT ProductID
         , Price
      FROM prices
     WHERE CustID = 12 
       AND GroupID  = 0
    UNION ALL
    SELECT ProductID
         , Price
      FROM prices
     WHERE GroupID = 2
       AND NOT EXISTS
           ( SELECT Price
               FROM prices
              WHERE CustID = 12 
                AND GroupID  = 0
                AND ProductID = prices.ProductID )
    does not work.
    Isn't there a variable, expression or function in the MySQL language that returns a column value from the currently 'active' row?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices
    does not work.
    well, yes -- because that's not what i suggested

    please take another look, i have edited my previous post to highlight in colour what you should have coded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    Thanks a lot r937,
    the 'AS foo ' escaped me. It does create a One Row temporary table within the statement, doesn't it?

    Now the result comes back to PHP as I intended.
    I was already thinking of coding the 'filter' in PHP, but I guess MySQL does a better job, performance-wise.

    It will take me some time to get the hang of it,
    your help is much appreciated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices
    It does create a One Row temporary table within the statement, doesn't it?
    no, it doesn't

    the second SELECT in the UNION has a correlated subquery

    this means that the row inside the subquery needs to refer to a value outside the subquery, which is what the correlation variable or table alias called foo allows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    PHP Code:
    $mySQLString"SELECT ProductID, Price FROM prices
     WHERE CustID = 
    $MyCustomerID 
       AND GroupID  = 
    $MyGroupID
    UNION ALL
    SELECT ProductID
         , Price
      FROM prices
     WHERE GroupID = 2
       AND NOT EXISTS
           ( SELECT Price FROM prices
              WHERE CustID = 
    $MyCustomerID 
                AND GroupID  = 
    $MyGroupID
                AND ProductID = 
    $MyProductIDIWantTofind )"
    PHP does a good job at spotting its variables inside a varaible assignment, however sometimes you may need to use string concatenation

    eg
    PHP Code:
    $mySQLString"SELECT ProductID, Price FROM prices
     WHERE CustID = "
    .$MyCustomerID.
       AND GroupID  = "
    .$MyGroupID."
    ...undsoweiter 
    remember if the column in the where clause is string/text/char then you need to encapsulate the parameter in quotes
    eg
    PHP Code:
    $mySQLString"SELECT ProductID, Price FROM prices
     WHERE CustType = \""
    .$MyCustomerID."\" 
       AND GroupType  = \""
    .$MyGroupID."\"
    ...undsoweiter
    ;"

    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2009
    Posts
    56
    I see . . .
    I find it very hard to get an understanding of MySQL by searching the Reference Manual.
    Can you recommend some good online MySQL material that makes the language transparent for a PHP, AS3, Flex etc. coder?

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Wouldn't it be more readable just to use single quotes on the strings ie for the last example:
    PHP Code:
    $mySQLString"SELECT ProductID, Price 
                   FROM   prices
                   WHERE  CustType = '
    $MyCustomerID
                          AND GroupType  = '
    $MyGroupID'
                          ...undsoweiter"


  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    PHP Code:
    AND ProductID $MyProductIDIWantTofind )"; 
    where does your $MyProductIDIWantTofind value come from?

    i guess you missed most of the thread where we developed the concept of the correlated subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2009
    Posts
    56
    @healdem
    Building the filter in PHP would involve searching the entire dataset for each and every GroupID related price.
    I would think that MySQL is more optimized than PHP searching an entire dataset 500 times.

  14. #14
    Join Date
    Jul 2009
    Posts
    56
    @r937
    I will have a look at your book.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    where does your $MyProductIDIWantTofind value come from?

    i guess you missed most of the thread where we developed the concept of the correlated subquery

    from a PHP variable set elsewhere in the PHP script
    was I was trying to suggest is that such parameters can be set easily enough within the SQL string
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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