Mysql select with joins problems...

[Dorinn]

Hello,

 

I have 3 tables in a database:

 

|------------| |-----------| |------------|

|Results | | Name | | Probes |

|------------| |-----------| |------------|

|id_results | |id_name | |id_probes |

|id_name | |name | |probes |

|id_probe |

 

I want to make a select on these that will have an ouput like that:

 

--------------------------------------------------------------------------------

Name | Probe1 | Probe2 ........

---------------------------------------------------------------------------------

Name1 | count of probe1 for Name1 | count of probe2 for Name1

Name2 | count of probe1 for Name2 | count of probe2 for Name2

.

.

How could I do this from php?

 

Many thanks....

[pallevillesen]

select n.name, count(p.id_probes)

from results r, name n, probes p

where r.id_name = n.id_name AND

r.id_probe = p.id_probes

group by n.name

order by n.name

 

Then use php to reformat...

 

(I\'m not sure my sql is absolutely correct, but try it).

 

How is the probe1 and probe2 defined ?

 

Examplify with some data ?

 

P.

[Dorinn]

The contents of the tables are something like this:

------------ ----------------- ----------------------------

Probes | Name | Results

------------ ----------------- ----------------------------

1 | Probe1 | 1 | Name1 | 1 | 1 | 1

------------- ------------------ ---------------------

2 | Probe2 | 2 | Name2 | 2 | 1 | 1

------------- ----------------- ---------------------

3 | Probe3 | 3 | Name3 | 3 | 1 | 2

------------- ---------------- -------------------

4 | Probe4 | etc. | 4 | 2 | 3

--------------- -------------------

etc. | 5 | 2 | 3

-------------------

| etc.

 

So the table results can hold combinations of tables \'Probes\' and \'Name\'.

 

I wrote a select like this:

----------------------------------------------------------------------------------

select t1.id_name, count(t2.id_probe) as A1, count(t3.id_probe) as A2, count(t4.id_probe) as A3, count(t5.id_probe) as B, count(t6.id_probe) as C, count(t7.id_probe) as D, count(t8.id_probe) as F

from results t1

 

left join results t2 on t1.id_club=t2.id_club and t2.id_probe=1

left join results t3 on t1.id_club=t3.id_club and t3.id_probe=2

left join results t4 on t1.id_club=t4.id_club and t4.id_probe=5

left join results t5 on t1.id_club=t5.id_club and t5.id_probe=3

left join results t6 on t1.id_club=t6.id_club and t6.id_probe=4

left join results t7 on t1.id_club=t7.id_club and t7.id_probe=6

left join results t8 on t1.id_club=t8.id_club and t8.id_probe=7

 

group by id_name

order by id_name;

-------------------------------------------------------------------------------------

but the problem is that I want to have the names on first column not the id\'s and to put a condition to return all the values where id_name is not null.

 

Many thanks