MySQL - How to SELECT based on value of another SELECT?

Posted by LaravelIndia - 1 year ago

Example 1:

SELECT * FROM tab_member WHERE MEMNO = (SELECT MEMNO FROM tab_member WHERE EmpName = 'john')

MySQL subquery in WHERE clause

SELECT 
    customerNumber, checkNumber, amount
FROM
    payments
WHERE
    amount = (SELECT 
            MAX(amount)
        FROM
            payments);

Example 2:

SELECT Name,
       SUM(Value) AS "SUM(VALUE)",
       SUM(Value) / totals.total AS "% of Total"
FROM   table1,
       (
           SELECT Name,
                  SUM(Value) AS total
           FROM   table1
           GROUP BY Name
       ) AS totals
WHERE  table1.Name = totals.Name
AND    Year BETWEEN 2000 AND 2001
GROUP BY Name;