Database Display

[maliary]

Hi,

 

I have a problem here that i would like you to help me out with.

I have got 5 tables.

 

a.)person

b.)encounter

c.)chem test

d.)bacterial test

e.)pathological test

 

the 3 test tables c,d,e and the encounter table b all have encounter values. The person table has person details like name etc

 

tables c,d and e contain diffrent tests.The encounter table also has a person id (pid) which matches with the person id in the person table to get the  person's details.

 

The encounter number in the test table matches with the encounter number in the encounter table which then matches with the pid in the same table to match with the pid in the person table and get the person details.

 

The logic may not be correct but I would like to make a query that displays the results for people with test data.How would I get around this?

 

this is what i have so far which is not working...

 

SELECT  *

FROM $tb_find_chemlab AS f,$tb_find_baclabor AS h,$tb_find_patho AS g

LEFT  JOIN $this->tb_enc AS e ON e.encounter_nr = f.encounter_nr  AND e.encounter_nr = h.encounter_nr AND e.encounter_nr = g.encounter_nr

LEFT  JOIN $this->tb_person AS p ON p.pid = e.pid

 

 

 

 

 

[jitesh]

The persons who have Chem test

 

select * FROM person,encounter,chem test WHERE chem test.eid = encounter.eid AND  encounter.pid = persond.pid

[maliary]

Thank very much jitesh, but i have done that. 

how do i do it for all the tests?

[jitesh]

The persons who have Chem test

 

select * FROM person,encounter,chem test,bacterial test,pathological test

WHERE encounter.eid = chem test.eid

AND chem test.eid = pathological test.eid

AND pathological test.eid = bacterial test.eid

AND  encounter.pid = persond.pid

[Barand]

SELECT p.pid, p.name, f.*, g.*, h.* 
FROM   $this->tb_person AS p
INNER JOIN  $this->tb_enc AS e ON p.pid = e.pid
LEFT JOIN $tb_find_chemlab AS f ON e.encounter_nr = f.encounter_nr
LEFT JOIN $tb_find_patho AS g ON e.encounter_nr = g.encounter_nr
LEFT JOIN $tb_find_baclabor AS h ON e.encounter_nr = h.encounter_nr

[Wildbug]

F&%^$'ing double post.... I just spent ten minutes writing a reply to the other one....

[maliary]

Hi,

 

Thank you very much  for the solution. I have tried it out but it only displays the encounter number for the last tests only -- that is $tb_find_baclabor --. It does not display the test type either for the other test tables apart from -- $tb_find_baclabor -- does the ordering of the joins matter?

 

It only seems to work correctly on that one table. how come?

 

Maliary

[maliary]

I have added the where clause below to reduce the output. But only test details from $tb_find_baclabor are viewable. help me display details from $tb_find_chemlab and $tb_find_patho.

 

 

SELECT p.pid, p.name_first, f.*, g.*, h.*

FROM  $tb_enc AS e

INNER JOIN $tb_person AS p  ON e.pid = p.pid

LEFT JOIN $tb_find_baclabor AS h ON e.encounter_nr = h.encounter_nr

LEFT JOIN $tb_find_patho AS g ON e.encounter_nr = g.encounter_nr

LEFT JOIN $tb_find_chemlab AS f ON e.encounter_nr = f.encounter_nr

 

WHERE e.encounter_nr = f.encounter_nr OR e.encounter_nr = g.encounter_nr OR e.encounter_nr = h.encounter_nr

 

[maliary]

It is still doing what my very first query was doing. Selecting on only one test table.

[Barand]

Can you attach a dump of some test data?

[maliary]

pig---------10000000                                ------------- from the $tb_find_chemlab

/pig---------10000000

/pig---------10000000

/pig---------10000000

/kib---13---Bacteriological Test---10000005    ----- from the $tb_find_baclabor

/test---14---Bacteriological Test---10000006

/lolgah---------10000008                              -------- from the $tb_find_patho

 

if I delete the " LEFT JOIN $tb_find_baclabor AS h ON e.encounter_nr = h.encounter_nr" it will show the full details for the table $tb_find_patho.

 

If i delete "LEFT JOIN $tb_find_patho AS g ON e.encounter_nr = g.encounter_nr" it will show the full details for the table $tb_find_chemlab : hence the conclusion that the query only works for one table.

 

 

display code is here :

 

while ($rows = mysql_fetch_assoc($result))

{

echo $rows['name_first'] .'---'. $rows['encounter_nr'].'---'.$rows['type'].'---'.$rows['pid'];

}

 

 

[fenway]

Topic continues over here on the MySQL board.