nadeemshafi9 Posted December 15, 2008 Share Posted December 15, 2008 hi guys i have 2 link tables for M:N relationships i need to select the data that is related to an RDS and the data has to be part of the SECTION in that RDS <?php function get_section_data($section_id){ $sql = "SELECT d.* FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "r.rds_id = ".$this->rds_id." AND ". "d.data_id = ( ". "SELECT d.data_id FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id." ". ");"; $result = mysql_query($sql); if($GLOBALS['sys_debug']) echo $sql." ".mysql_error(); return $result; } ?> i got good marks 100% for my SQL test but that was ages ago now i cant get my head around it thanks for nay help Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/ Share on other sites More sharing options...
nadeemshafi9 Posted December 15, 2008 Author Share Posted December 15, 2008 here are teh tables CREATE TABLE `data_table` ( `data_id` int(11) NOT NULL auto_increment, `data_lang` varchar(255) default '0', `data_project` int(11) default '0', `data_createdate` timestamp(14) NOT NULL, `data_createby` int(11) default '0', `data_moddate` timestamp(14) NOT NULL default '00000000000000', `data_modby` int(11) default '0', `data_status` enum('current','pending','archived') default 'current', `data_title` varchar(255) default NULL, `data_text` text, PRIMARY KEY (`data_id`) ) TYPE=MyISAM AUTO_INCREMENT=4 ; CREATE TABLE `rds_table` ( `rds_id` int(11) NOT NULL auto_increment, `rds_lang` varchar(255) default NULL, `rds_project` int(11) default NULL, `rds_zone` int(11) default NULL, `rds_createdate` int(11) default NULL, `rds_createby` varchar(255) default NULL, `rds_moddate` timestamp(14) NOT NULL, `rds_modby` int(11) default NULL, `rds_status` enum('current','pending','archived') default NULL, `rds_title` varchar(255) default NULL, `rds_pri_rel` int(11) default NULL, `rds_sec_rel` int(11) default NULL, PRIMARY KEY (`rds_id`) ) TYPE=MyISAM AUTO_INCREMENT=17 ; CREATE TABLE `section_table` ( `section_id` int(11) NOT NULL auto_increment, `section_lang` int(11) default NULL, `section_createdate` timestamp(14) NOT NULL, `section_createby` int(11) default NULL, `section_moddate` timestamp(14) NOT NULL default '00000000000000', `section_modby` int(11) default NULL, `section_status` enum('current','pending','archived') default NULL, `section_title` varchar(255) default NULL, `section_text` text, PRIMARY KEY (`section_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; CREATE TABLE `_data_section_link` ( `_id` int(11) NOT NULL auto_increment, `section_id` int(11) NOT NULL default '0', `data_id` int(11) NOT NULL default '0', PRIMARY KEY (`_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; CREATE TABLE `_rds_section_link` ( `_id` int(11) NOT NULL auto_increment, `section_id` int(11) NOT NULL default '0', `rds_id` int(11) NOT NULL default '0', PRIMARY KEY (`_id`) ) TYPE=MyISAM AUTO_INCREMENT=10 ; Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-715688 Share on other sites More sharing options...
nadeemshafi9 Posted December 15, 2008 Author Share Posted December 15, 2008 an RDS is a room data sheet Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-715689 Share on other sites More sharing options...
nadeemshafi9 Posted December 15, 2008 Author Share Posted December 15, 2008 You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT d.data_id FROM data_table d, section_table s, _data_sect Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-715691 Share on other sites More sharing options...
nadeemshafi9 Posted December 15, 2008 Author Share Posted December 15, 2008 iv tested both queries individualy they work function get_section_data($section_id){ $sql = "SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id." AND ". "d.data_id IN (". "SELECT d.data_id FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "r.rds_id = ".$this->rds_id. ")"; $result = mysql_query($sql); echo $sql." ".mysql_error(); return $result; } still no luck like thsi thiogh Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-715892 Share on other sites More sharing options...
fenway Posted December 15, 2008 Share Posted December 15, 2008 First, echo the query. Second, what's not working? Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-716012 Share on other sites More sharing options...
nadeemshafi9 Posted December 17, 2008 Author Share Posted December 17, 2008 First, echo the query. Second, what's not working? it fails on the subquery and says the error is not on the ( but on the select , iv tried the subquery on its own and it works, the error im getting is first it fails on the subquery and says you have an error exactly on the select, and obviously returns an invalid results array wich dosent work with fetch array. Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-717568 Share on other sites More sharing options...
nadeemshafi9 Posted December 17, 2008 Author Share Posted December 17, 2008 here is the echo SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl WHERE d.data_id = _dsl.data_id AND s.section_id = _dsl.section_id AND s.section_id = 1 AND d.data_id IN (SELECT d.data_id FROM rds_table r, data_table d, _data_rds_link _drl WHERE d.data_id = _drl.data_id AND r.rds_id = _drl.rds_id AND d.data_status = 'current' AND r.rds_id = 1) here is another one from the list its meant to produce SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl WHERE d.data_id = _dsl.data_id AND s.section_id = _dsl.section_id AND s.section_id = 2 AND d.data_id IN (SELECT d.data_id FROM rds_table r, data_table d, _data_rds_link _drl WHERE d.data_id = _drl.data_id AND r.rds_id = _drl.rds_id AND d.data_status = 'current' AND r.rds_id = 1) Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-717570 Share on other sites More sharing options...
nadeemshafi9 Posted December 17, 2008 Author Share Posted December 17, 2008 RELATIONS - the data is related to the sections and and also related to teh rds, each section is displayed in every rds the data for that section is detrmined by selecting all data related to that section wich will reurn data that is related to other rds's but then we need to filter data for that section by saying look we are in this rds so show us data for this section thats related to this rds, ???? lol data is related to RDS's RDS's are made up of sections data is related to sections and further filtered by there relation to RDS's first i get all the data for the section wich will get me data related to all rds's SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl WHERE d.data_id = _dsl.data_id AND s.section_id = _dsl.section_id AND s.section_id = 1 then i whant to filter this data in this section by the RDS (room data sheet) i am currently in AND d.data_id IN (SELECT d.data_id FROM rds_table r, data_table d, _data_rds_link _drl WHERE d.data_id = _drl.data_id AND r.rds_id = _drl.rds_id AND d.data_status = 'current' AND r.rds_id = 1) data is archived on update aswell thats why i have current so dont worry about that Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-717591 Share on other sites More sharing options...
nadeemshafi9 Posted December 17, 2008 Author Share Posted December 17, 2008 this is what i whant but its not working /*$sql = "SELECT d.* FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "d.data_id IN (". "SELECT d.data_id FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id. ") AND ". "r.rds_id = ".$this->rds_id;*/ so im having to do it manualy $sql = "SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id; $result = mysql_query($sql); if($GLOBALS['sys_debug']) echo $sql." ".mysql_error(); while($data = mysql_fetch_array($result)){ $condition .= ",".$data['data_id']; } $sql = "SELECT d.* FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "d.data_id IN (0".$condition.") AND ". "r.rds_id = ".$this->rds_id; $result = mysql_query($sql); if($GLOBALS['sys_debug']) echo $sql." ".mysql_error(); return $result; Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-717630 Share on other sites More sharing options...
fenway Posted December 17, 2008 Share Posted December 17, 2008 Which version of mysql are you using -- checking for subquery support here. Either way, it would be easier to re-write this using a dervied table. Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-718059 Share on other sites More sharing options...
nadeemshafi9 Posted December 19, 2008 Author Share Posted December 19, 2008 Which version of mysql are you using -- checking for subquery support here. Either way, it would be easier to re-write this using a dervied table. i re wrote it using php cos its just not working with a subquery, its mysql 4.0.21, well i got it working with php subquery but i need teh proper thing working heres the working code. <?php function get_section_data($section_id){ // the sub query is not working so i have to do it manualy /*$sql = "SELECT d.* FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "d.data_id = Any (". "SELECT d.data_id FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id. ") AND ". "r.rds_id = ".$this->rds_id;*/ $sql = "SELECT d.* FROM data_table d, section_table s, _data_section_link _dsl ". "WHERE ". "d.data_id = _dsl.data_id AND ". "s.section_id = _dsl.section_id AND ". "s.section_id = ".$section_id; $result = mysql_query($sql); if($GLOBALS['sys_debug']) echo $sql." ".mysql_error(); while($data = mysql_fetch_array($result)){ $condition .= ",".$data['data_id']; } $sql = "SELECT d.* FROM rds_table r, data_table d, _data_rds_link _drl ". "WHERE ". "d.data_id = _drl.data_id AND ". "r.rds_id = _drl.rds_id AND ". "d.data_status = 'current' AND ". "d.data_id IN (0".$condition.") AND ". "r.rds_id = ".$this->rds_id; $result = mysql_query($sql); if($GLOBALS['sys_debug']) echo $sql." ".mysql_error(); return $result; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-719593 Share on other sites More sharing options...
nadeemshafi9 Posted December 19, 2008 Author Share Posted December 19, 2008 Which version of mysql are you using -- checking for subquery support here. Either way, it would be easier to re-write this using a dervied table. why would it be better to derive a new table ? Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-719596 Share on other sites More sharing options...
fenway Posted December 19, 2008 Share Posted December 19, 2008 well i got it working with php subquery but i need teh proper thing working heres the working code. There is no such thing as a "php subquery" -- you mean a nested loop, which is bad. And yes, pre4.1, no subqueries. That's why we ask you to post your mysql version at the outset. Which version of mysql are you using -- checking for subquery support here. Either way, it would be easier to re-write this using a dervied table. why would it be better to derive a new table ? So that you don't have funny subquery issues with syntax or optimization or execution. Try this -- also, you should get used to using proper ANSI join syntax, makes it so much easier to read: SELECT d.* FROM data_table AS d INNER JOIN _data_section_link AS _dsl USING ( data_id ) INNER JOIN section_table AS s USING( section_id ) INNER JOIN ( SELECT d.data_id FROM rds_table AS r INNER JOIN data_table AS d USING ( data_id ) INNER JOIN _data_rds_link AS _drl USING ( rds_id ) WHERE d.data_status = 'current' AND r.rds_id = 1 ) AS derived USING( data_id ) WHERE s.section_id = 2 Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-719663 Share on other sites More sharing options...
nadeemshafi9 Posted January 1, 2009 Author Share Posted January 1, 2009 ok il try it after the holidays lol mery christmas and happy new year Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-727346 Share on other sites More sharing options...
nadeemshafi9 Posted January 5, 2009 Author Share Posted January 5, 2009 OK this is the same error i had with the normal specific join above it basicaly rejects the SELECT in the sub query if i use str8 out numbers eg in (1,2,3) it works but if i do a IN (SELECT) it breaks. Il try it on mysql 5 on the live server SELECT d. * FROM data_table AS d INNER JOIN _data_section_link AS _dsl USING ( data_id ) INNER JOIN section_table AS s USING ( section_id ) INNER JOIN ( SELECT d.data_id FROM rds_table AS r INNER JOIN data_table AS d USING ( data_id ) INNER JOIN _data_rds_link AS _drl USING ( rds_id ) WHERE d.data_status = 'current' AND r.rds_id =1 ) AS derived USING ( data_id ) WHERE s.section_id =1 LIMIT 0 , 30 MySQL said: Documentation #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT d.data_id FROM rds_table AS r INNER JOIN data_table AS d Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729788 Share on other sites More sharing options...
nadeemshafi9 Posted January 5, 2009 Author Share Posted January 5, 2009 damn as u said pre 4.1 no sub query well luckily its just my testing dev machine the live machines php 5 so il have to upgrade prob is i dont bother iwth binaries cos i have a mac and its unfortunatly power pc and the others in the team have a damn iontel wich has more software available i may have to ask for a air book lol Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729789 Share on other sites More sharing options...
fenway Posted January 5, 2009 Share Posted January 5, 2009 That's not a subquery, that's a derived table... should be supported. Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729841 Share on other sites More sharing options...
nadeemshafi9 Posted January 5, 2009 Author Share Posted January 5, 2009 That's not a subquery, that's a derived table... should be supported. no it fails on the acua That's not a subquery, that's a derived table... should be supported. im prety sure USING ( section_id ) INNER JOIN ( SELECT d.data_id FROM rds_table AS r INNER JOIN data_table AS d USING ( data_id ) INNER JOIN _data_rds_link AS _drl USING ( rds_id ) WHERE d.data_status = 'current' AND r.rds_id =1 ) AS derived USING ( data_id ) this part is a sub query even thogh the table is a dervied table ????, it inner joins to a subquery Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729909 Share on other sites More sharing options...
fenway Posted January 5, 2009 Share Posted January 5, 2009 Yeah, guess so.... I thought 4.0 had support for those, but I guess not. Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729937 Share on other sites More sharing options...
nadeemshafi9 Posted January 5, 2009 Author Share Posted January 5, 2009 Yeah, guess so.... I thought 4.0 had support for those, but I guess not. thanks thogh u helped clear up alot of stuff for me , thanks much appreciated Quote Link to comment https://forums.phpfreaks.com/topic/137032-solved-need-to-seleect-data-from-two-joins-cant-see-issue-in-my-sql/#findComment-729951 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.