Record Combination

[stanley1610]

I have a table in this format:

Name: Varchar
Food: Varchar
Date: Date

There's no primary key.
e.g. Sam eats icecream on 19 Sep 2007, then the record will store
Sam, icecream, 19 Sep 2007.

If the case:
Sam, icecream, 19 Sep 2007.
Susan, icecream, 20 Sep 2007.
Jacky, icecream, 21 Sep 2007.


Then Susan and Jacky did "repeat eat" because Susan and Jacky repeat the same food "icecream" as Sam within 14 days.

My report (in JasperReports) will draw how many repeat eat there is. The result will look like:

Report A -
FOOD, NUMBER OF REPEAT
icecream, 2
orange, 3

Report B -
NAME, FOOD NUMBER OF REPEAT
Sam, biscuit, 1
Sam, orange, 1
Susan, biscuit, 1

The most critical challenge is the number of records in the table around 20,000. If combining them directly, it will be around 400,000,000 records in immediate table.

How can I do the reports? Many thanks.

 

[guelphdad]

1) what have you tried for queries so far. we don't answer homework questions though we can guide you to an answer so you learn while YOU do the work

2) note that all tables should have a primary key otherwise how do you tell the data apart?

If you have
Sam, icecream, 19 Sep 2007.
Sam, icecream, 19 Sep 2007.
Sam, icecream, 19 Sep 2007.
Susan, icecream, 20 Sep 2007.
Jacky, icecream, 21 Sep 2007.

how do you know which row you want for Sam?

your primary key can be all three columns at once

PRIMARY KEY(person, food, date) for instance.

3) don't store your dates in that format, you will lose all date functions. store them in a date field as yyyymmdd and use DATE_FORMAT to display them as you have above if needed