Topic: 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....                    

[biopv]

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