Intersection of two MySql queries

Question

I have a table where each data has a date when it was inserted in form of two columns, year and week:

+------+------+-------+
| Week | Year | Value |
+------+------+-------+
| 1    | 2014 | 5     |
| 5    | 2014 | 23    |
| 6    | 2014 | 12    |
| 7    | 2014 | 43    |
| 8    | 2014 | 4     |
| 9    | 2014 | 2     |
| 26   | 2013 | 21    |
| 27   | 2013 | 17    |
| 28   | 2013 | 42    |
| 31   | 2013 | 5     |
| ...  | ...  | ..    |
+------+------+-------+

I need a query to get data that was inserted between two dates (year, week). I guess that it should be alternative to intersection of two queries, one with a starting date and the second with ending data, but I can't get it to work. Any help or sugestion?

Here is my shot but INTERSECT is not supported in MySql:

(SELECT SUM(Duration), Week, Type, Year 
 FROM UP26rotordowntime 
 WHERE (Year>=2013) 
 AND (Week >=01) 
 GROUP BY Week)

INTERSECT

(SELECT SUM(Duration), Week, Type, Year 
 FROM UP26rotordowntime 
 WHERE (Year<=2014) 
 AND (Week <=14) 
 GROUP BY Week)

Not sure I got you. What's wrong with `WHERE Year BETWEEN 2013 AND 2014` ? — Alma Do, Mar 04 '14 at 08:44
@AlmaDo It will not give good results since Year and Week need to be "connected" somehow. — justRadojko, Mar 04 '14 at 08:51
@bksi in that case with second query I would include results that are discarded in the first one. — justRadojko, Mar 04 '14 at 08:53
did you saw this post? http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql — bksi, Mar 04 '14 at 08:59
It is always good to have some primary key that you can use with IN clause. In that case your query will be very easy to construct — bksi, Mar 04 '14 at 09:06
I have that as ID column but trying to figure out how to construct all of these info — justRadojko, Mar 04 '14 at 09:09

score 1 · Accepted Answer · answered Mar 04 '14 at 09:43

1

You can put simple logic in WHERE conditions and use (year,week) pairs for GROUP BY:

SELECT SUM(Duration), Week, Type, Year 
FROM UP26rotordowntime 
WHERE Year = 2005 AND Week >= 10
   OR Year BETWEEN 2006 AND 2013
   OR Year = 2014 AND Week <= 14
GROUP BY Year,Week

answered Mar 04 '14 at 09:43

piotrm

12,038
4
31
28

It's close.. but in this case I'm getting some weeks that I dont have in table??!? Which is totally confusing. And Week 7,8 and 9 are not included. – justRadojko Mar 04 '14 at 09:51
Its just an example for more than two years, in your case just skip middle condition and put exact values you need. – piotrm Mar 04 '14 at 09:57
I realize that, but still this is including all data since you are using `OR` so that data which is discarded with `WHERE Year = 2005 AND Week >= 10` will be included with `Year = 2014 AND Week <= 14` – justRadojko Mar 04 '14 at 10:01
Note equality sign on year. Basically you split data into three parts: some weeks in the first year (and first year only), then all data from years in the middle, then some weeks from the last year (and last year only). – piotrm Mar 04 '14 at 10:11

bksi · Answer 2 · 2014-03-04T10:15:35.183

0

If you have id the query is very simple:

SELECT SUM(Duration), Week, Type, Year FROM UP26rotordowntime 
WHERE ID IN (
  SELECT ID FROM UP26rotordowntime WHERE (Year>=2013) AND (Week >=01) )
OR ID IN (
  SELECT ID FROM UP26rotordowntime WHERE (Year<=2014) AND (Week <=14))
GROUP BY Week

This should return you the intersect you need

edited Mar 04 '14 at 10:15

answered Mar 04 '14 at 09:18

bksi

1,606
1
23
45

With this query I'm getting only data from 2014, none from 2013 is included :/ – justRadojko Mar 04 '14 at 09:26
I think that the only solution should be INNER JOIN but I don't know how to construct it since it should join queries from same table – justRadojko Mar 04 '14 at 09:38
Sorry my mistake, you should use OR instead AND. See my edited answer – bksi Mar 04 '14 at 10:15

Intersection of two MySql queries

2 Answers2