2

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.

0

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.