CODEWITHSUNDEEP
phpmysql
Mr Cathode
Mr Cathode

Reputation: 77

How to select a column value based on two row conditions?

Name      Username    Day worked  Time period  
----------------------------------------------
John Doe  john        Sunday      8
Tom       tom         Monday      10
Jane Doe  jane        Tuesday     5
John Doe  john        Friday      9

Query:

SELECT username FROM table WHERE (day_worked='Sunday' AND time_period>5) AND (day_worked='Friday' and time_period>8)

Here I am applying conditions for two columns, user who worked on Sunday more than 5 hours and Friday more than 8 hours. So obviously John Doe (Username: john) meets the condition and it should be outputted.

However this does not return anything. May be the logic I am using is wrong. Any ideas?

Upvotes: 1

Views: 200

Answers (3)

Strawberry
Strawberry

Reputation: 33945

SELECT username 
  FROM table 
 WHERE (day_worked='Sunday' AND time_period>5)         
    OR (day_worked='Friday' and time_period>8) 
 GROUP 
    BY username
HAVING COUNT(*) = 2

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26343

You can do this by a bit of cross-tabulation magic. First off, cross tabulate all of your users by Sunday and Friday:

SELECT
  username,
  SUM(CASE WHEN `Day worked` = 'Sunday' THEN `Time Period` END) As Sunday,
  SUM(CASE WHEN `Day worked` = 'Friday' THEN `Time Period` END) AS Friday
FROM table
GROUP BY username

This query will yield the following:

username  Sunday  Friday
--------  ------  ------
jane      NULL    NULL
john      8       9
tom       NULL    NULL

By pushing this result down into a subquery you can easily pick out who worked more than 5 hours on Sunday and more than 8 hours on Friday:

 SELECT username
 FROM (
   SELECT
     username,
      SUM(CASE WHEN `Day worked` = 'Sunday' THEN `Time Period` END) As Sunday,
      SUM(CASE WHEN `Day worked` = 'Friday' THEN `Time Period` END) AS Friday
    FROM table
    GROUP BY username
  ) SunFri
WHERE SunFri.Sunday > 5 AND SunFri.Friday > 8

Upvotes: 2

Nandakumar V
Nandakumar V

Reputation: 4625

Can you check if this query is gives you the desired result 

SELECT username FROM table 
INNER JOIN table T2 ON table.Username = T2.Username AND (day_worked='Friday' and time_period>8)
WHERE (day_worked='Sunday' AND time_period>5)

Its better to post a sqlfiddle for us to work with.

Upvotes: 0

Related Questions