Populating an empty table using loops, conditionals, and accessing multiple tables

Question

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.

Thank you.

Answer 1

I might be misunderstanding your question; however this should work. (The code is written for SQL Server since I don't have MySQL experience.)

CREATE TABLE TableA (PersonID INT, Points decimal(10,2));
CREATE TABLE TableB (PersonID INT, Points decimal(10,2));
CREATE TABLE TableC (UID INT, Seas INT, Wk INT, V char(3), H char(3));
CREATE TABLE TableD (PersonID INT, Grp char(3));
CREATE TABLE NewTable (PersonID INT, Points decimal(10,2), 
        Points_Alt decimal(10,2), Pattern int);

INSERT INTO TableA VALUES (1, 10);
INSERT INTO TableB VALUES (1, 23);
INSERT INTO TableA VALUES (2, 11);
INSERT INTO TableB VALUES (2, 24);
INSERT INTO TableC VALUES (3, 2011, 10, 3, 4)
INSERT INTO TableC VALUES (4, 2011, 10, 5, 6)
INSERT INTO TableD VALUES (1, 3);
INSERT INTO TableD VALUES (2, 6);

INSERT INTO NewTable (PersonID, Points, Points_Alt)
SELECT TableA.PersonID, TableA.Points, TableB.Points AS Points_Alt
FROM TableA
    INNER JOIN TableB ON TableA.PersonID = TableB.PersonID;

UPDATE NewTable 
SET Pattern = 3
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011 
    AND TableC.Wk = 10
    AND TableC.V = TableD.Grp;

UPDATE NewTable 
SET Pattern = 6
FROM NewTable INNER JOIN TableD ON NewTable.PersonID = TableD.PersonID
    , TableC
WHERE TableC.Seas = 2011 
    AND TableC.Wk = 10
    AND TableC.H = TableD.Grp;

SELECT * FROM NewTable

The first statement creates the data in NewTable and the 2 remaining statements set Pattern to the particular value based on either H or V as in your pseudo-code.