[Swansea Hackspace] SQL Help

[Steven Whitehouse]

Hi,

On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
> Hi all,
> 
> 
> Is anyone coming tonight that has good SQL skills?
> 
> 
> Im having a problem with some joins and subquery type stuff.  Just to
> clarify though, I can do what I am trying to achieve by using php as
> some glue for the SQL, and now im up to a 25-35 seconds for the
> queries to execute and my fake CRON doesnt like it - i get an email
> warning at 30, and the process is killed at 35 seconds.
> 
> 
> I believe it is achievable in MUCH less time doing all the work at the
> database end - just now I have had to resort to creating a cache for
> the results when it server load is low enough that it lets me process
> in under 30 seconds :(
> 
> 
> if it helps the table format is something like this
> 
> 
> table1
> table1.id: autonum
> active: bool
> t2id_1: int
> t2id_2: int
> 
> t2id_3: int
> 
> t2id_4: int
> 
> t2id_5: int
> 
> t2id_6: int
> 
> 
> 
> table2
> table2.id: autonum
> expected_date:  datetime
> actual_date:   datetime
> claim_date: datetime
> value: datetime
> 
> 
> 
> 
> what i need is
> for every item in table1 that is active, get the items from table2
> that match t2id_1 ... t2id_6, from this i need a count and value  for
> the next 'x' months where the claim_date is blank
> 
> 
I suspect that your data is structured wrongly... for table one you
might be able to rearrange like this:

table1
table1.id: autonum
active: bool
t2id: int
kind: int

So that now you only need to join the tables on one field. The new kind
column then tells you whether the entry is for t2id_1... t2id_6. That
would make it much simpler. I don't know what "active" is for, so maybe
you need to break that into two tables, if there should be only one
"active" per table 6 entries.

Anyway, once you've done your join, then you should be able to order by
months, and add a limit so that you only get the next N entries. You
could also use a where clause to limit the number returned too, as it
was not very clear as to which would be preferable in this case,

Steve.


> [month]['count'] = 3
> 
> [month]['value'] = 600
> 
> 
> [month+n]['count'] = 7
> 
> [month+n]['value'] = 450
> 
> 
> an so on..
> 
> 
> I will have my laptop with me so i can demonstrate the actual database
> format, and the results I desire if anyone can help :)
> 
> 
> Thanks
> 
> 
> G
> _______________________________________________
> Hackspace mailing list
> Hackspace at swansea.hackspace.org.uk
> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace