maliary Posted May 2, 2007 Share Posted May 2, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/ Share on other sites More sharing options...
fenway Posted May 2, 2007 Share Posted May 2, 2007 Why? This is the simple and clean version. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-243736 Share on other sites More sharing options...
maliary Posted May 3, 2007 Author Share Posted May 3, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-244092 Share on other sites More sharing options...
fenway Posted May 3, 2007 Share Posted May 3, 2007 So that query works? Show us the query that didn't work. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-244454 Share on other sites More sharing options...
maliary Posted May 4, 2007 Author Share Posted May 4, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-245123 Share on other sites More sharing options...
fenway Posted May 4, 2007 Share Posted May 4, 2007 Wrap your ON clause in parens -- the one with the ORs. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-245601 Share on other sites More sharing options...
maliary Posted May 5, 2007 Author Share Posted May 5, 2007 I tried it does'nt work. I need help on this. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-246035 Share on other sites More sharing options...
fenway Posted May 5, 2007 Share Posted May 5, 2007 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"? Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-246103 Share on other sites More sharing options...
maliary Posted May 7, 2007 Author Share Posted May 7, 2007 hi, Please see my post on php help. Maliary Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-247126 Share on other sites More sharing options...
fenway Posted May 7, 2007 Share Posted May 7, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-247273 Share on other sites More sharing options...
fenway Posted May 8, 2007 Share Posted May 8, 2007 I've unlocked this topic -- please summarize what you've found out so far. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-248482 Share on other sites More sharing options...
maliary Posted May 9, 2007 Author Share Posted May 9, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-248749 Share on other sites More sharing options...
bubblegum.anarchy Posted May 9, 2007 Share Posted May 9, 2007 is $tb_find_baclabor a variable or the table name? Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-248797 Share on other sites More sharing options...
maliary Posted May 9, 2007 Author Share Posted May 9, 2007 Its a variable referring to a table name. So is $tbl_find_chemlab , $tbl_find_patho,$tbl_person and $tbl_enc. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249000 Share on other sites More sharing options...
bubblegum.anarchy Posted May 10, 2007 Share Posted May 10, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249448 Share on other sites More sharing options...
btherl Posted May 10, 2007 Share Posted May 10, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249558 Share on other sites More sharing options...
maliary Posted May 10, 2007 Author Share Posted May 10, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249586 Share on other sites More sharing options...
bubblegum.anarchy Posted May 10, 2007 Share Posted May 10, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249599 Share on other sites More sharing options...
maliary Posted May 10, 2007 Author Share Posted May 10, 2007 Yes It does. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249626 Share on other sites More sharing options...
bubblegum.anarchy Posted May 10, 2007 Share Posted May 10, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249694 Share on other sites More sharing options...
maliary Posted May 10, 2007 Author Share Posted May 10, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249725 Share on other sites More sharing options...
fenway Posted May 10, 2007 Share Posted May 10, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-249841 Share on other sites More sharing options...
bubblegum.anarchy Posted May 11, 2007 Share Posted May 11, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-250234 Share on other sites More sharing options...
maliary Posted May 11, 2007 Author Share Posted May 11, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-250331 Share on other sites More sharing options...
maliary Posted May 12, 2007 Author Share Posted May 12, 2007 ok, So how do we go about this? Quote Link to comment https://forums.phpfreaks.com/topic/49668-sql-conversion/#findComment-251203 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.