Jump to content

sql joins


hyster

Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.