3

I have a number of tables I am trying to combine with joins but as such, the results are returned in a number of rows whereas I would like to have them generated as new columns.

member_information Table

MemberID | FirstName | LastName --------------------------------- 1 | John | Harris 2 | Sarah | Thompson 3 | Zack | Lewis

member_dependent_information Table

MemberID | FirstName | LastName | Type --------------------------------------- 1 | Amy | Harris | 1 2 | Bryan | Thompson | 1 2 | Dewey | Thompson | 2 2 | Tom | Thompson | 2 3 | Harry | Lewis | 2 3 | Minka | Lewis | 1

MySQL Query:

SELECT t1.FirstName, t1.LastName, t1.MemberID, IF(t2.Type = '1',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Spouse_Name, IF(t2.Type = '2',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Child_Name, FROM member_dependent_information t2 INNER JOIN member_information t1 USING (MemberID) ORDER BY t1.LastName ASC, t1.MemberID ASC;

Ideal Results

MemberID | FirstName | LastName | Spouse_Name | Child_Name1 | Child_Name2 -------------------------------------------------------------------------------- 1 | John | Harris | Amy Harris | NULL | NULL 2 | Sarah | Thompson | Bryan Thompson | Dewey Thompson | Tom Thompson 3 | Zack | Lewis | Mika Lewis | Harry Lewis | NULL

ACTUAL RESULTS

MemberID | FirstName | LastName | Spouse_Name | Child_Name ------------------------------------------------------------------- 1 | John | Harris | Amy Harris | NULL 2 | Sarah | Thompson | Bryan Thompson | NULL 2 | Sarah | Thompson | NULL | Dewey Thompson 2 | Sarah | Thompson | NULL | Tom Thompson 3 | Zack | Lewis | Mika Lewis | NULL 3 | Zack | Lewis | NULL | Harry Lewis

While my query returns the "correct" data in multiple rows, it does not combine the result into one single row as needed.

The suggestion for Pivot Tables / Crosstabs has been mentioned below but every reference I am able to find suggests using mathematic calculations or that the number of fields to be returned is known. I will not know this information as a single member COULD have up to 100 dependents (although more like 4-8)

UPDATE #1

I feel I am getting closer to the final solution. I added the function GROUP_CONCAT to my query which returns ALL firstnames in a single column and ALL last names in a single column but still need to break them out into their own individual columns.

New function is:

SELECT t1.MemberID, t1.FirstName, t1.LastName, GROUP_CONCAT(t2.FirstName) AS Dep_Firstnames, GROUP_CONCAT(t2.LastName) AS Dep_LastNames FROM member_information t1 LEFT OUTER JOIN member_dependent_information t2 ON t1.MemberID = t2.MemberID WHERE t1.Status = 1 GROUP BY t1.MemberID
Improve this question
2
  • Where is the t1.Status = 1 comes from? Commented Dec 2, 2010 at 20:22
  • It has to do with a live member, another field in the table. My example above should encompass the overall idea though Commented Dec 2, 2010 at 21:55

3 Answers 3

Reset to default
13

Sometimes the first step to solving your problem is knowing what it's called. After that, it's simply a matter of googling. What you are trying to create is called a pivot table or crosstab report. Here is a link explaining how to create pivot tables in MySQL. And here is a more in depth tutorial.

UPDATE:

Now that you've updated the question, I have a clearer idea of what you are trying to accomplish. I'll give you an alternative solution which is similar but not exactly what you want based on MySQL's GROUP_CONCAT function.

select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName)) from member_information as t1 left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID group by t1.MemberID;

I've verified this query as follows. First the setup:

create table member_information ( MemberID int unsigned auto_increment primary key, FirstName varchar(32) not null, LastName varchar(32) not null ) engine=innodb; create table member_dependent_information ( MemberID int unsigned not null, FirstName varchar(32) not null, LastName varchar(32) not null, Type int unsigned not null, foreign key (MemberID) references member_information(MemberID) ) engine=innodb; insert into member_information (MemberID, FirstName, LastName) values (1, 'John', 'Harris'), (2, 'Sarah', 'Thompson'), (3, 'Zack', 'Lewis'); insert into member_dependent_information (MemberID, FirstName, LastName, `Type`) values (1, 'Amy', 'Harris', 1), (2, 'Bryan', 'Thompson', 1), (2, 'Dewey', 'Thompson', 2), (2, 'Tom', 'Thompson', 2), (3, 'Harry', 'Lewis', 2), (3, 'Minka', 'Lewis', 1);

And now the query and results:

mysql> select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))from member_information as t1 -> left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID -> group by t1.MemberID; +-----------+----------+------------------------------------------------------+ | FirstName | LastName | group_concat(concat(t2.FirstName, ' ', t2.LastName)) | +-----------+----------+------------------------------------------------------+ | John | Harris | Amy Harris | | Sarah | Thompson | Bryan Thompson,Dewey Thompson,Tom Thompson | | Zack | Lewis | Harry Lewis,Minka Lewis | +-----------+----------+------------------------------------------------------+ 3 rows in set (0.00 sec)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@Asaph - First, thank you for laying out what I'm looking for. The Wiki provided doesn't really fit what I am trying to accomplish as the example provided has a one to one relationship for the items in each row. Even if you were to add another row for "Bob", it would only sum as if the test score could be added so exam 1's score was 101+. I will keep looking into your other tutorial.
@JM4: No problem. I think your problem is perhaps a subtle variation of what is presented in the first tutorial. Hopefully the second one will help you dial in your problem more precisely. If not, let me know. In any case, I think your biggest initial hurdle was not knowing what search term to google. So I'm glad I was helpful for that.
@Asaph - every page I have read in the samples provided as well as google'ing the terms mentioned usually reference figures which are summed and used as a single value which is not really what I'm trying to accomplish. I know this can be done with a combination of mysql and php fairly easily but I am trying to avoid using PHP at all as this will sit on another individual's machine and run locally only. In every example provided, the programmer knows the columns which should be returned, whereas I would not in my example. I could have 0 descriptions or 1000 for each row.
ultimately, the column creation would occur as a loop (i.e. return the number of row results found from table 3, then list every description from t3 as a column in resultset
at second thought - programming in php would not be as simple either given, the foreach loop while sifting through table 3 would stop at row end, then possibly leave several columns <td></td> off the returned data.
|
1

I'm not sure how the OP transformed from having programID and Status to how it is now, but the closest thing I would be able to get is (which does not require pivot tables):

SELECT t1.MemberID, t1.FirstName, t1.LastName, concat(t2.FirstName, ' ', t2.LastName) as Spouse_Name, group_concat(concat(t3.FirstName, ' ', t3.LastName) ORDER BY t3.FirstName) as Children_names FROM member_information t1 LEFT JOIN member_dependent_information t2 ON (t1.MemberID=t2.MemberID AND t2.Type=1) LEFT JOIN member_dependent_information t3 ON (t1.MemberID=t3.MemberID and t3.Type=2) GROUP BY MemberID;

Which produces:

+----------+-----------+----------+----------------+-----------------------------+ | MemberID | FirstName | LastName | Spouse_Name | Children_names | +----------+-----------+----------+----------------+-----------------------------+ | 1 | John | Harris | Amy Harris | NULL | | 2 | Sarah | Thompson | Bryan Thompson | Dewey Thompson,Tom Thompson | | 3 | Zack | Lewis | Minka Lewis | Harry Lewis | +----------+-----------+----------+----------------+-----------------------------+

and it would be "easy" with any programming language to extract that Children_names into separate columns.

Improve this answer

2 Comments

per question, not looking to use any external program other than a mysql query unless I am told it is impossible.
@JM4: Since you tagged php and pdo, I assumed they are to be used.
0

This is not exactly what you are looking for, but it might be a step in the right direction.

SELECT Table1.memberIDs, Table1.firstname, Table1.lastnames, Table2.programIDs, Table3.description FROM Table1, Table2, Table3 WHERE Table1.memberIDs = Table2.memberIDs AND Table2.programIDs = Table3.programID
Improve this answer

1 Comment

this query is exactly what I am trying to avoid and hence my question. The resultset returned by this query would return multiple rows if multiple programIDs exist (which they would).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.