Filtering Result Rows

vivoices · #1 (**permalink**) 07-20-09, 06:33

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

r937 · #2 (**permalink**) 07-20-09, 06:54

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

vivoices · #3 (**permalink**) 07-20-09, 07:31

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 ??

r937 · #4 (**permalink**) 07-20-09, 07:39

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 )

vivoices · #5 (**permalink**) 07-20-09, 07:47

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?

r937 · #6 (**permalink**) 07-20-09, 07:52

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

vivoices · #7 (**permalink**) 07-20-09, 08:07

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.

r937 · #8 (**permalink**) 07-20-09, 08:11

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

healdem · #9 (**permalink**) 07-20-09, 08:14

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
;";

vivoices · #10 (**permalink**) 07-20-09, 08:21

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?

mike_bike_kite · #11 (**permalink**) 07-20-09, 08:21

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";

r937 · #12 (**permalink**) 07-20-09, 08:28

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

vivoices · #13 (**permalink**) 07-20-09, 08:31

@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.

vivoices · #14 (**permalink**) 07-20-09, 08:35

@r937
I will have a look at your book.

healdem · #15 (**permalink**) 07-20-09, 08:50

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

Internet Services

Web Development

Digital Marketing

Consumer Tech