Jump to content


Photo

Mysql select with joins problems...


  • Please log in to reply
2 replies to this topic

#1 Dorinn

Dorinn
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 May 2003 - 08:38 AM

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

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 21 May 2003 - 09:22 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 Dorinn

Dorinn
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 21 May 2003 - 10:24 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users