Multiple related table fields as result columns (cross-tabs?)

[zq29]

I'm trying to write a query that returns all of the related rows from one table as columns/fields in the result set of a table join. I'm not sure if that makes sense or not, but hopefully my schema below and the result I am trying to achieve can explain it better:

 

 

CREATE TABLE `case_study` (
  `id` int( NOT NULL auto_increment,
  `title` varchar(160) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE TABLE `case_study_header` (
  `id` int(3) NOT NULL auto_increment,
  `name` varchar(160) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM ;

CREATE TABLE `case_study_content` (
  `id` int( NOT NULL auto_increment,
  `case_study` int( NOT NULL,
  `header` int(3) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM ;

INSERT INTO `case_study_header` (`id`,`name`) VALUES ('1','Header 1'), ('2','Header 2'), ('3','Header 3');

INSERT INTO `case_study` (`id`,`title`) VALUES ('1','My Case Study');

INSERT INTO `case_study_content` (`id`,`case_study`,`header`,`content`) VALUES ('1','1','1','Some content'), ('2','1','3','Some more content');

 

Trying to get the result:

 

idtitleHeader 1Header 2Header 3

1My Case StudySome contentNULLSome more content

 

I have tried referencing the case_study_content table multiple times in the query with different aliases, but that both didn't work, and wouldn't really be too great as there could be changes in the case_study_header table in the future and the query would need to be manually changed.

 

I feel it might have something to do with sub queries, unions or something of that nature?

 

Does anyone understand what I am trying to achieve, and have you any input?

[fenway]

I know exactly what you want -- cross-tabs.

 

 

[zq29]

Thanks fenway, looks to be what I'm after - I'll read it through fully in a while.

[zq29]

fenway, have you worked with cross-tabs in this fashion before? All of the information I can find demonstrate using cross-tabs to count data (SUM(IF(`x`=`y`,1,0)) as `foo`), not to return rows of text.

 

My attempt at getting it to return text is as follows:

SELECT csc.`case_study`, c.`title`

, IF(csc.`header` = 1, `content`,'') as `summary`
, IF(csc.`header` = 2, `content`,'') as `background`
, IF(csc.`header` = 3, `content`,'') as `approach`

FROM `case_study` as c INNER JOIN `case_study_content` as csc ON c.`id`=csc.`case_study`
GROUP BY c.`id`

 

...which returns the first bit of content (summary), but the other two fields empty.

 

idtitlesummarybackgroundappraoch

1My Case StudySome content[/td][td]

 

Instead of:

idtitlesummarybackgroundapproach

1My Case StudySome contentNULLSome more content

[fenway]

Yeah, I see that now... obviously, you can tell that csc.header can only be one value at a time!

 

You'll need to join in the header table once per header row entry value with the name in the on clause -- let me know if I've lost you.