Jump to content

SQL conversion


maliary

Recommended Posts

Hi,

Kindly help me convert the following sql statement by removing the joins and having a simple sql statement instead.

 

 

$this->sql="SELECT f.encounter_nr, e.encounter_class_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.sex

FROM $this->tb_find_chemlab AS f

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

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

Maliary.

Link to comment
Share on other sites

  • Replies 57
  • Created
  • Last Reply

 

 

I am trying to create a search script that goes over 2 additional tables. I tried introducing one to the query above but nothing worked.So I thought having a script without the joins would do the trick.

Let me explain it a little further.

 

"SELECT f.encounter_nr, e.encounter_class_nr, p.pid, p.name_last, p.name_first, p.date_birth, p.sex

FROM $this->tb_find_chemlab AS f

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

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

 

the tb_find_chemlab has chemical tests, i need to add tb_find_baclab and tb_find_patho for bacterial and pathological tests respectively. how would i do this with the above statement.

 

Moreover is there a simpler solution for this?

 

Thanks,

Maliary

 

 

Link to comment
Share on other sites

Hi,

 

SELECT  *

FROM $this->tb_find_baclabor AS f ,$this->tb_find_chemlab AS g, $this->tb_find_patho AS h

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

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

 

this is the query that is causing problems. how do i get it to display?

 

Link to comment
Share on other sites

Hi,

 

SELECT  *

FROM $this->tb_find_baclabor AS f ,$this->tb_find_chemlab AS g, $this->tb_find_patho AS h

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

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

 

this is the query that is causing problems. how do i get it to display?

 

Causing problems how? And "display"?

Link to comment
Share on other sites

This is the code -- but it isn't working as required.

 

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

 

It only works on one table $tb_find_baclabor . From the dump data only data from the table is displayed

 

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 $tb_find_patho

 

Maliary

 

 

 

 

Link to comment
Share on other sites

 

With the actual table names :-

 

 

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

FROM  $tb_enc AS e

INNER JOIN table_person AS p  ON e.pid = p.pid

LEFT JOIN table_bac AS h ON e.encounter_nr = h.encounter_nr

LEFT JOIN table_patho AS g ON e.encounter_nr = g.encounter_nr

LEFT JOIN table_chem 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.

 

 

Link to comment
Share on other sites

Does the following query also produce expected result?

 

SELECT p.pid, p.name_first
FROM table_enc AS e
     INNER JOIN table_person AS p ON e.pid = p.pid
     LEFT JOIN table_bac AS h ON e.encounter_nr = h.encounter_nr
     LEFT JOIN table_patho AS g ON e.encounter_nr = g.encounter_nr
     LEFT JOIN table_chem AS f ON e.encounter_nr = f.encounter_nr
WHERE h.encounter_nr IS NOT NULL OR g.encounter_nr IS NOT NULL OR f.encounter_nr IS NOT NULL

Link to comment
Share on other sites

 

Well,

 

It does display the required records but only the data from the table_person.

 

Hence, where it should display this

 

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

 

It only displays $rows['name_first'] and $rows['pid']. But not the rest which are from the test tables.

 

sample of the dump.

 

pig---------10000000

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

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

 

 

 

Link to comment
Share on other sites

fenway may appreciate the code block with the actual table names rather than the variables, well I would anyway, but maybe fenway is not finding this entire thread as confusing as I am.

No kidding... I won't even touch a thread until I see the actual statement that the server receives -- echo this PHP string.

Link to comment
Share on other sites

Ok,

 

I made ammendments

 

SELECT p.*,g.*,f.*,h.*,e.*
FROM table_enc AS e
     INNER JOIN table_person AS p ON e.pid = p.pid
     LEFT JOIN table_bac AS h ON e.encounter_nr = h.encounter_nr
     LEFT JOIN table_patho AS g ON e.encounter_nr = g.encounter_nr
     LEFT JOIN table_chem AS f ON e.encounter_nr = f.encounter_nr
WHERE h.encounter_nr IS NOT NULL OR g.encounter_nr IS NOT NULL OR f.encounter_nr IS NOT NULL

 

Only data from table_chem is displayed. I put the

LEFT JOIN table_bac AS h ON e.encounter_nr = h.encounter_nr

below and made changes in the SELECT and WHERE statements.

 

then only data from table_bac was displayed. Why does it SELECT from only one test table?

 

 

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.