Hi,

I need a query resulting in a variable number of fields, based upon the number of records in a table.

Consider the following (reduced) problem:
I have 3 tables:

t1 (Unique Key k1, field A);
t2 (Unique key k2, field B);
t3(Keys k1,k2, field C); // k1,k2 unique as a combination

I need a query that returns me records like

B Cn1 Cn2 Cn3 etc.

Cnx are all C from t3 where t3.k2=t2.k2
Cnx sorted by A and NULL when k1,k2 is not present in t3.
The final result sorted by B

Example:
t1:
1 A1
2 A3
3 A2

t2:
1 B1
2 B3
3 B2

t3:
1 1 C1
1 2 C2
2 1 C3
3 2 C4

Query result should be
B1 C1 NULL C2
B2 NULL NULL C4
B3 C3 NULL NULL

If you need I can show what I tried, but at this moment I don't want my (obviously wrong, because I failed so far) queries obstruct any good ideas from you.

Thanks for any help that points me in the good direction.


I'm going to try to rephrase the question, then provide an answer.   That way, if I have it right it will be clear and if I have it wrong - well - it will be a good answer anyway, all be it to a differentr question.

""Table T1 defines a series of rows, and table T2 a series of columns in a rectangular grid.  Each cell in the grid contains a NULL, except where tabel T3 contains a value that's to go in a cell.   Each row of T3 contains a row id, a column id, and a value.""

In a less abstract way, your result will have columns for each of the hairdressers in town, rows for each of the women in the town who have their hair cut, and the data you'll hold will be the ranking that each woman gives each hairdresser, bearing in mind that not all women have had their hair cut by every hairdresser.

If the question is correct, the bad news is that a regular query isn't going to do it for you, as the definition of the number of columns you need is variable (variable number of rows, great - variable number of columns - "control, we have a problem!").  However, it's possible with a few lines of code.

Here's the SQL code to set up an example, and to read back that data along each row - you then just need to add the code to work out the final matrix ...

Code:

drop database if exists rupert; create database rupert; use rupert; create table dressers (did int, name text); create table women (wid int, name text); create table ranking (rid int, did int, wid int, score int); insert into dressers values (1, "Hairtek"), (2,"Strictly His"),        (3,"Glow"), (4,"Studio Two"); insert into women values(1,"Grace"), (2,"Ruby"), (3,"Olivia"),        (4,"Emily"), (5,"Jessica"), (6,"Sophie"); # Top names from: # http://www.statistics.gov.uk/specials/babiesnames_girls.asp insert into ranking values (1, 1, 1, 10), (2,3,1,5),        (3,2,5,7), (4,3,4,6); select women.name, women.wid, ranking.did, ranking.score from        ranking join women on women.wid = ranking.wid        order by women.wid, ranking.did;

Result:

Code:

+---------+------+------+-------+ | name    | wid  | did  | score | +---------+------+------+-------+ | Grace   |    1 |    1 |    10 | | Grace   |    1 |    3 |     5 | | Emily   |    4 |    3 |     6 | | Jessica |    5 |    2 |     7 | +---------+------+------+-------+

Yes, your rephrase of the problem is right.

And I already have my php code that generates a report with a variable number of columns.
In that code I have a SELECT to get all the hairdressers (to follow your example)
Another SELECT to get all the women to generate the heading of the report. And I generate a temporary array to get the women indexes sorted on the names.
As I walk down the first selection to generate the rows I use a SELECT on the ranking to get the rankings for the hairdresser 'at hand'.
I now walk down this second selection to get the columns, meanwhile stepping through the array to know when to insert a NULL.

But I wondered whether there exists an SQL way of doing this. With the use of a JOINs and multiple SELECTs and probably 'for loops' etc. That part of SQL syntax I haven't used yet, so maybe I better study that first.

---