sql joins

[hyster]

I have 5 tables with identical layouts, the point being I have a snap shot of a players game stats for the previous 4 weeks.

the tables will be used to work out how many games a player has had week by week.

 

the search will pull "select * where clan = '123456' "

use the ac_id to tie the tables but I need cw and sh from the 4 other tables

the end result I'm after is

 

name  -   past 7 - past 14 - past 21 - past 28

hyster -     10   -    15     -      23     -      30

play1  -      3    -      6       -    10      -     17 

 

the problem I have is that I do not know what kinda join to use as none seem to work in phpmyadmin

TABLES
monitor
  acc_id - name -cw - sh - date - clan

monitor_7
  acc_id - name -cw - sh - date - clan

monitor_14
  acc_id - name -cw - sh - date - clan

monitor_21
  acc_id - name -cw - sh - date - clan

monitor_28
  acc_id - name -cw - sh - date - clan

this returns null for the 4 tables so I don't know where I'm going wrong :/

pointers please if u would be so kind :$

SELECT * FROM 'monitor' where 'clan' = '123456'
left JOIN monitor_7
ON
monitor.acc_id=monitor_7.acc_id
left JOIN monitor_14
ON
monitor.acc_id=monitor_14.acc_id
left JOIN monitor_21
ON
monitor.acc_id=monitor_21.acc_id
left JOIN monitor_28
ON
monitor.acc_id=monitor_28.acc_id

[Barand]

Table and column names should not be in single quotes.

 

I would have expected you get an error message when you ran it in phpmyadmin.

[mac_gyver]

JOIN's are used when there is a relationship between different meaning data in tables. an example would be a clan table, that defines the clan name and assigns an id to each clan and a table holding the data you currently have. if you want to retrieve the clan name for display or search for data using the clan name, you would join the two tables using the clan_id columns.

 

for what you are doing, you should have ONE table holding all the results. the date column you have will let you retrieve the data you want. If you want to retrieve the n most recent weeks worth of data, you would add a term in the WHERE clause to match rows having a date value greater then or equal to the starting date you are interested in.

[hyster]

the 'clan' is number (500035013, 500017963), the name will be hard coded as only 7 clans will be used as its for an internal web page.

 

date is for the last game played not when the data was updated

 

currently the update script moves the data from 1 table to the next.

so id be better doing it like this ??

 

acc_id - name - clan - date - sh_cur - sh_7 - sh_14 sh_21 sh_28 cw_cur - cw_7 - cw_21 - cw_28

[mac_gyver]

no, you wouldn't have columns like that. i reviewed your last database related thread and a member mentioned Normalization. this is the same thing.

 

short answer - there's one row in a table for each item of data and all the same meaning data is in the same table. by storing the data correctly, you can write simple queries that find any data that you want. storing the data correctly will also eliminate all the code you have to move data between tables.

 

something tells me that the data you are showing us in this thread is actually derived/accumulated data. you should be calculating this when needed, not storing it in a table.