Jump to content

[SOLVED] need to seleect data from two joins cant see issue in my sql

nadeemshafi9

By nadeemshafi9
in MySQL Help

 Share

Recommended Posts

nadeemshafi9 Advanced Member

nadeemshafi9

Posted
Posted

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

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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 ;

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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)

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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;

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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;
}
?>

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

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

Link to comment
Share on other sites
  • 2 weeks later...
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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

Link to comment
Share on other sites
nadeemshafi9 Advanced Member

nadeemshafi9

Posted
  • Author
Posted

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

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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
Go to topic listing
×
×
  • 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.