SQL conversion

[maliary]

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.

[fenway]

Why? This is the simple and clean version.

[maliary]

 

 

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

 

 

[fenway]

So that query works? Show us the query that didn't work.

[maliary]

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?

 

[fenway]

Wrap your ON clause in parens -- the one with the ORs.

[maliary]

I tried it does'nt work. I need help on this.

[fenway]

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"?

[maliary]

hi,

 

Please see my post on php help.

 

Maliary

[fenway]

hi,

 

Please see my post on php help.

 

Maliary

Seriously? You double-posted, and it sounds like you're getting help over there, so this topic is locked.

[fenway]

I've unlocked this topic -- please summarize what you've found out so far.

[maliary]

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

 

 

 

 

[bubblegum.anarchy]

is $tb_find_baclabor a variable or the table name?

[maliary]

Its a  variable referring to a table name.

 

So is $tbl_find_chemlab , $tbl_find_patho,$tbl_person and $tbl_enc.

[bubblegum.anarchy]

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.

[btherl]

What results do you get if you do onle one of the left joins at one time?  For each table you are left joining with.

 

That is, 3 seperate queries.

[maliary]

 

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.

 

 

[bubblegum.anarchy]

Does the following query produce expected results?

 

SELECT p.pid, p.name_first
FROM table_enc AS e
     INNER JOIN table_person AS p ON e.pid = p.pid

[maliary]

 

Yes It does.

[bubblegum.anarchy]

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

[maliary]

 

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

 

 

 

[fenway]

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.

[bubblegum.anarchy]

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

Holy Crap dude, off coarse SELECT p.pid, p.name_first is only going to display that information.

 

[maliary]

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?

 

 

 

[maliary]

ok,

 

So how do we go about this?