Jump to content

SQL conversion


maliary
 Share

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

Top Posters In This Topic

 

 

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

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share


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