CODEWITHSUNDEEP
mysqldate
user894932
user894932

Reputation:

SQL: how select all rows where a "count > 1" for certain fields

I have 1 table, that has records by date. I need to compare data from year 1 to year 2 (last year), but in some cases a few records in year 1 don't exist in year 2 and some in year 2 do not exist in year 1.

I only care about those that match. my structure is:

F_DATE F_TEXT1 F_TEXT2 F_NUMBER1 F_NUMBER2
2014-01-01 bob sue 19 12
2013-04-19 bob sue 12 11
2013-06-01 bob jane 5 6
2014-11-28 jane bob 4 4
2014-03-12 mike bob 8 1

so in the above example only care about the records that contain bob + sue.

I can identify records by concat(F_TEXT1,F_TEXT2) as f_compare to get bobsue i can then count on this field having count(*) > 1 but doing this doesn't work well because F_DATE is unique to the pairing, and F_NUMBER1 and F_NUMBER2 are fundamental for further processing.

I am joining to another table, which has a F_LABEL for the years and a Start Date (F_SDATE) and End Date (F_EDATE) column to provide a nice label for the years.

I am having difficulty getting my records into a query so i can the process them further.

I have tried to select everything i need - this query gives the extra records from year 1 and year 2. and i also select a F_DATE from T_SOMETABLE which is basically for config so this query can be updated to compare year 3 to 4 etc by changing F_SOMEVALUE.

SELECT F_LABEL, F_TEXT1, F_TEXT2, F_NUMBER1, F_NUMBER2, fix
FROM (
SELECT b.F_LABEL, a.F_TEXT1, a.F_TEXT2, a.F_HGOALS, a.F_AGOALS, 
concat(F_HOME,'-',F_AWAY) as fix FROM all_records a, some_labels b
WHERE a.F_DATE > b.F_SDATE
AND a.F_DATE < b.F_EDATE
AND a.F_DATE > (SELECT F_DATE FROM T_SOMETABLE WHERE F_SOMEVALUE='1')
UNION ALL
SELECT F_LABEL, F_TEXT1, F_TEXT2, F_NUMBER1, F_NUMBER2, 
concat(F_TEXT1,'-',F_AWAY) as fix
FROM all_records a, some_labels b
WHERE a.F_DATE > b.F_SDATE
AND a.F_DATE < b.F_EDATE
AND a.F_DATE > (SELECT F_DATE FROM T_SOMETABLE WHERE F_SOMEVALUE='2')
AND a.F_DATE < (SELECT F_DATE FROM T_SOMETABLE WHERE F_SOMEVALUE='1')
) z
ORDER BY F_TEXT1, F_TEXT2, F_LABEL

I can't get my head round select from year 2 where the concat (above) exists in year 1 and then selecting the F_LABEL, F_TEXT1, F_TEXT2, F_NUMBER1 and F_NUMBER2from both years into one table.

Can you help point me in the right direction?

Bonus points if this query can go into a VIEW (nested statements don't help here) so the query/table doesn't need to be recreated every time a new record is added.

The query needs to be perform well as the output would appear on a webpage.

Upvotes: 0

Views: 291

Answers (1)

M. Page
M. Page

Reputation: 2814

It is difficult to give a precise answer but, in order to compare a year with the previous one, you need to join your table with itself (t1 and t2 below are two occurrences of yourtable), to form a query like this:

SELECT ...
FROM yourtable t1, yourtable t2
WHERE (t1.F_TEXT1 = t2.F_TEXT1 
OR t1.F_TEXT2 = t2.F_TEXT2)
AND YEAR(t1.F_DATE) = YEAR(t2.F_DATE) - 1

Upvotes: 1

Related Questions