Nested Query

[lexer]

Hi

I've to Mysql tables that as follow:

Table 1 called "typeofcall" has one field called "TypeOfCallE" and has 3 registers:
CALLSACCEPTED
CallAnswer
ABANDONEDNO

And Table 2 called report2 and has 3 fields: "CALLSACCEPTED", "CallAnswer" and "ABANDONEDNO" with folowing data:
CALLSACCEPTED CallAnswer ABANDONEDNO
3 2 1
4 2 2

I'm trying to make a first query on Table 1 (typeofcall) an according to each result of first query, doing a second query (nested query) and total fields on table 2 "report2": addition result has to be: CALLSACCEPTED=7, CallAnswer=4 and ABANDONEDNO=3

       //First Query 
	$query1 = "SELECT TypeOfCallE".
	 "FROM typeofcall ";
	 "ORDER BY typeofcall DESC ";
	$result1 = mysql_query($query1) or die(mysql_error());	

	while($row1 = mysql_fetch_array($result1))
	{

	//Nested Query 2 According to First Query
	$query2 = "SELECT typeofcall .typeofcallE, report2.CALLSACCEPTED, report2.CallAnswer, 
        report2.ABANDONEDNO ".
	"FROM typeofcall , report2 ";

	$result2 = mysql_query($query2) or die(mysql_error());	
	
	while($row2 = mysql_fetch_array($result2))
	{
                        //Total Call Accepted
	                if ($row1['TypeOfCallE']=="CALLSACCEPTED")
			{	
                  	    $TotalAccep=$TotalAccep+$row2['CALLSACCEPTED'];				
			}
                        //TotalCall Answer
                        if ($row1['TypeOfCallE']=="CallAnswer")
			{
				$TotalCallAnswer=$TotalCallAnswer+$row2['CallAnswer'];				
			}
                        //Total Call Aband.
                        if ($row1['TypeOfCallE']=="CallAnswer")
			{
				$TotalCallAband=$TotalCallAband+$row2['ABANDONEDNO'];				
			}
			
	}	// Second Query

} // Firts Query

echo $TotalAccep;
echo "-";
echo $TotalCallAnswer;
echo "-";
echo $TotalCallAband;
echo "-";
?>

I can see the results but multiplied by 3, for example: $TotalAccep= 21 (It has to be 7).
Please, any Ideas?

 

[Olaf Doschke]

You said:

a 'FULL' join and a 'CROSS' join ARE the same thing, only the name changes

And you even took the time to format it this way to stress out it is the same. Yet, it isn't. And I just showed that, with a code example. Sometimes people knowing things come up with a code example, not as answer to your own code example, your argument about that is just pointless.

I didn't even take your first thought in that sense of PHP doing a full join. That would only apply, if you would query one table in each query, yet the second query queries both tables. And then it would be a cross join, not a full join.

Bye, Olaf.

 

[ChrisHirst]

As I have already stated, ... I was NOT providing a treatise on the intricacies of the functional operation of either type of join, it was not intended to be part of a doctoral thesis or dissertation. It was, and still is a light weight comment that both perform the SAME BASIC OPERATION. That is joining the records/rows in BOTH tables. END OF COMPARISON.

Sure there are differences in the results that each join will return; ON THEIR RESPECTIVE DATABASE SERVERS and given that a

FULL JOIN

cannot be performed instead of a

CROSS JOIN

, and a

CROSS JOIN

cannot be performed instead of a

FULL JOIN

the differences are of little consequence with respect to THIS THREAD.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[Olaf Doschke]

There's no need to shout. So I assume you already knew all the differences. That's what you want to say. Fine, I don't believe that, but fine. Besides that, you're again wrong, but this time I spare to expand on that.

Bye, Olaf.

 

[ChrisHirst]

You are still missing the point. Granted, IF I were being technically precise it would be wrong, but I was not being technically precise, it was intended merely as an allegorical comparison, a figurative representation of the two.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[Olaf Doschke]

I understood this is your excuse. But you said they are the same, stressed out bold. That means you thought and said they are exactly the same. It's always easy to say you didn't mean something the way it was interpreted, but my last point is not about being technically wrong. As said I don't want to expand on this.

Bye, Olaf.

 

[ChrisHirst]

Sorry, I can't do anything about your delusion that you can tell precisely what someone that you do not know is thinking at an earlier point in time.



Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.