This question pertains to a MySQL database, although, in the end, this may be database neutral.
I am trying to populate an empty table (let's call it NewTable) that I created. The data population is based upon retrieving a bunch of data from other tables, along with occasional checking of that data to create a given value. The crux is that I want to get this data for every person found in a given table, let's call it table A. And there are conditionals involved too as I retrieve some of this data. Here is some pseudocode for it….
For each personID in TableA select statistic from a column in TableA for that personID as column 1 of NewTable select statistic from a column in TableB for that personID as column 2 of NewTable select statistic from a column in TableC based upon a value for that personID found in another TableD If selected statistic has value X, then assign column 3 of NewTable a certain value; otherwise assign column 3 of NewTable another value : : End Loop after data retrieved for last personID in TableA
NewTable should now be populated after this loop ends.
Here is a bit more specific information based on the comment below:
**TableA:** PersonID : char(6) Points : Decimal(3,1) **TableB:** PersonID : char(6) Points : Decimal(3,1) **TableC:** UID : tinyint(6) Seas : int(10000) Wk : tinyint(20) V : char(3) H : char(3) **TableD:** PersonID : char(6) Grp : char(3) **NewTable:** PersonID: char(6) Points : Decimal(3,1) Points_Alt : Decimal(3,1) Pattern : tinyint(6) For each PersonID AS current in TableA: SET current AS PersonID IN NewTable SELECT Points FROM TableA WHERE PersonID = current AS Points IN NewTable SELECT Points FROM TableB WHERE PersonID = current AS Points_Alt IN NewTable For each UID in TableC WHERE Seas = 2011 AND Wk = 10: if V == SELECT Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then SET 3 AS Pattern IN NewTable else if H = select Grp FROM TableD WHERE TableA.PersonID = TableD.PersonID Then SET 6 AS Pattern IN NewTable End Loop End Loop
Can anyone provide me an example of how to do this using a SQL query? Some actual example SQL code would be very helpful.