Page 1 of 1

minor JOIN problem

Posted: Fri Feb 24, 2012 4:12 am
by glennnall
i'm a newbie with JOINS, but i'm learning.

at the moment I'm having a problem with a query returning duplicate results:

two tables:

Code: Select all

a_child
ch_id   u_id   name
   1      2     David


a_subjects
sub_id   ch_id   sub_name
  1          1        English
  2          1        History

  
		  
$result = mysql_query("SELECT c.ch_id, c.name, s.sub_name
FROM a_child c
INNER JOIN a_subjects s ON (c.ch_id = s.ch_id) WHERE c.u_id = 2") or die(mysql_error());

 
		  while ($child = mysql_fetch_array($result)) {
			  
			  echo $child['name'].", ";
			  
		  }
for some reason this keeps giving me "David, David," and i cannot figure out why - i've rearranged the query as best i can, and i've tried different JOINS (i'm still learning, sorry)...

can someone show me what's wrong and why i'm getting duplicated results? i'm grateful.

GN

Re: minor JOIN problem

Posted: Fri Feb 24, 2012 4:16 am
by glennnall
i realize i probably don't need a JOIN for this little query, but it's actually a much larger query with several tables that's giving me problems and i'm really just trying to get to know how JOINS work.

thanks very much for anyone's help.

this is the entire query, giving me the same duplicate results

Code: Select all


$result = mysql_query("SELECT c.name, s.sub_name, t.hrs_reqd, ts.hrs_spent, ts.work_desc
FROM a_topic_stuff ts
INNER JOIN a_child c ON (c.ch_id = ts.ch_id)
INNER JOIN a_subjects s ON (s.sub_id = ts.sub_id)
INNER JOIN a_topics t ON (t.t_id = ts.t_id) WHERE c.u_id = '2'") or die(mysql_error());

a_topic_stuff
id  ch_id  sub_id  t_id  hrs_spent  work_desc
1     1        1         1        2         yadda yadda
2     1        1         1        1         yadda yadda



Re: minor JOIN problem

Posted: Fri Feb 24, 2012 6:27 am
by mikosiko
two tables:
Syntax: [ Download ] [ Hide ] [ Select ]
a_child
ch_id u_id name
1 2 David

a_subjects
sub_id ch_id sub_name
1 1 English
2 1 History

$result = mysql_query("SELECT c.ch_id, c.name, s.sub_name
FROM a_child c
INNER JOIN a_subjects s ON (c.ch_id = s.ch_id) WHERE c.u_id = 2") or die(mysql_error());
while ($child = mysql_fetch_array($result)) {

echo $child['name'].", ";
}
for some reason this keeps giving me "David, David," and i cannot figure out why
your query is correct and it is returning what is supposed to return... just change your echo to this to see:

Code: Select all

 echo $child['name'] . ", " . $child['sub_name'] . "<br />";
An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The SQL-engine computes the Cartesian product of all records in the tables. Thus, processing combines each record in table A with every record in table B. Only those records in the joined table that satisfy the join predicate remain, and the final result set will be the composed of the fields that you choose from each table.

In your example the final result set will look like this
1 David English
1 David History

then is up to you to decide how you want to display the results. Same concept apply with more tables