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?

 

[ChrisHirst]

Please, any Ideas?

You are using the wrong handed / wrong type of join.

http://www.w3schools.com/sql/sql_join.asp

Scroll down to:

Different SQL JOINs
Before we continue with examples, we will list the types of the different SQL JOINs you can use:

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables

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.

 

[vacunita]

What you show is not really a nested Query, its just 2 separate queries one being run based on the results of the other by PHP.

As such, it does not really belong in the mysql forum as your actual queries are working.

But your PHP logic may be having an issue.


I suggest you post this in the PHP forum where it can be addressed properly from the PHP side. forum434


fyi nested queries would be something like: SELECT * FROM tablename where id in (SELECT * FROM tablename2 where xxx)... in other words a query inside another query, both running at the same time.



@Chris:

What joins???? There are no joins.



----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[ChrisHirst]

What joins???? There are no joins.

Effectively there are, it's a 'FULL JOIN' performed using PHP, and therefore; as this IS the MySQL forum (as you have pointed out) appropriate.

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]

SELECT ... FROM typeofcall , report2

This is NOT a full join, this is a cross join, yet another join type not at all mentioned.

Full join:

http://www.w3schools.com/sql/sql_join_full.asp

A cross join is even less often used than a full join, as it combines every row of one with every row of another table. There is no join condition and that's equivalent to "true" as join condition. A full join still has a join condition and doesn't pair any two records, only a) matching and b) non matches of both sides.

With a cross join of one table with N rows and the other with M rows you get N*M rows, a full join has (unless there are duplicate keys) only max(N,M) rows, either N or M, not N*M.

Bye, Olaf.

 

[lexer]

Thanks for your answers!!!!

 

[lexer]

Hi, I'm using mysql, Mysql doesn't supoort FULL JOIN, I think I have to use LEFT, RIGHT JOIN and UNION

 

[ChrisHirst]

UNION is not really a 'JOIN', it is a way to concatenate multiple SELECT queries from one table into a single row set.

I would suggest that a 'LEFT JOIN' is what you are after.

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]

MySQL indeed doesn't supports FULL JOIN, see

https://dev.mysql.com/doc/refman/5.7/en/join.html

But you don't need a full join. You need a simple LEFT JOIN, nothing special.

SELECT * FROM parenttable LEFT JOIN childtable ON childtable.parentID = parenttable.ID

A syntx like that, not just comma separated table names. You need a key of your typeofcall table in the report2 table, how else would you know what and how to join?

Bye, Olaf.

 

[lexer]

Thanks!!!! I just want to total every field in table 2 (called "report2", table 2 has 3 fields CALLSACCEPTED,CallAnswer ABANDONEDNO)

The field CALLSACCEPTED has the following data:
3
4

The field CallAnswer has the following data:
2
2

The field ABANDONEDNO has the following data:
1
2


I want to totalize every field doing just one query and every field totalized save it in a variable, I have to obtain 3 variables like this:

$TotalCALLSACCEPTED=7
$TotalCallAnswer=4
$TotalCallABANDONEDNO=3

 

[lexer]

Thanks, I found the solution using SUM, This is what I did:

	$query1 = "SELECT SUM(report2.CALLSACCEPTED) AS CALLSACCEPTEDSum, SUM(report2.CallAnswer) AS CallAnswerSum, 	 SUM(report2.ABANDONEDNO) AS ABANDONEDNOSum   ".
	"FROM report2 ";

	$result1 = mysql_query($query1) or die(mysql_error());	


	while($row1 = mysql_fetch_array($result1))
	{
		$CALLSACCEPTED=$row1[CALLSACCEPTEDSum];
		$CallAnswer=$row1[CallAnswerSum];
		$ABANDONEDNO=$row1[ABANDONEDNOSum];
		
	}
//Show Reults
echo $CALLSACCEPTED;
echo "<br>";
echo $CallAnswer;
echo "<br>";
echo $ABANDONEDNO;
echo "<br>";

 

[Olaf Doschke]

Yes, you don't need to qurey from typeofcall when you only want to sum report2 data. Why did you query FROM typeofcall , report2 in the first place?

Bye, Olaf.

 

[lexer]

Because I thought that I had to do a first query for selecting every column on the second query

 

[Olaf Doschke]

You took "in the first place" literally.

Your second query queries FROM typeofcall , report2.

This is not - as Chris said - a full join - but a cross join. It turns out you only need to query report2. But what is the common denominator of typeofcall and report2 making you think you need to involve both tables in your query/queries? If you only want to select fields from one table, your FROM clause seldom needs other tables, unless you want to filter data.

Bye, Olaf.

 

[ChrisHirst]

Let's not get hung up on the semantics here, a 'FULL' join and a 'CROSS' join ARE the same thing, only the name changes.

MySQL (and Oracle) use 'CROSS' because it fits in the LEFT/RIGHT data source table identifier scheme, MsSQL uses 'FULL' to be descriptive of the operation. Other than that it's a bit like quibbling over rowset vs recordset, They are simply labels for the same thing.

Perhaps I should have use the appropriate term, BUT the post in question was not a technical description of the correct query to use, but a 'philosophical' one regarding the resultant set of records due to the PHP code 'joining' BOTH tables into one set of rows/records ... therefore a 'full' join, the capitals are for EMPHASIS not code.

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]

No, Chris, then you have to learn something about the diffference of FULL and CROSS joins. Let me take this exercise, you never can learn this early enough.

FULL JOIN (something MySQL doesn't offer, as lexer correctly quoted) and Cross Join in comparison, done in T-SQL (MSSQL Server):

Declare @Customers as Table (CustomerID int, CustomerName varchar(50));
Declare @Orders as Table (OrderID int, CustomerID int);

Insert Into @Customers Values (1,'Alfreds Futterkiste'),(2,'Ana Trujillo Emparedados y helados'),(3,'Antonio Moreno Taquería');
Insert Into @Orders Values (10308, 2), (10309, 37), (10265, 3), (10310, 77);

--Full Join (with a join condition). Result contains data of both sides, even where there is no join
SELECT CustomerName, OrderID
FROM @Customers C FULL OUTER JOIN @Orders O ON C.CustomerID=O.CustomerID;

--Cross Join or cartesian product
SELECT CustomerName, OrderID
FROM @Customers, @Orders;

See the queries, see the results?
Upper query and upper result are full join, lower query and lower result are cross join.

As I already said a full join HAS a join condition, in comparison the cross join simply creates every possible combination of rows, whether they make sense or not. For example order 10308 is of CustomerID 2 (Ana Trujillo Emparedados y helados) and only that customer ID 2, but the lower cross join combines it with every customer. The nature of the full join shows up in comparison with a left or right join. In detail about this (I won't do the screenshots for that), the left join would have result rows 1-3 from the first result, all customers and only their orders, the right join, would have rows 2-5 in it, all orders and to which customers they belong, full join combines this. The result has all orders and all customers, but combinations are only done, where it makes sense. We have partial data, in which Alfreds Futterkiste has no orders and the orders 10309 and 13010 are orphaned, both this shows up in a full join. Last not least an inner join of both tables would only contain rows 2 and 3, those where there is a join from both sides and no side is blank.

Chris, I hope you learn from this, just looking at the sets and set theory is not enough to understand the fine differences of join types. But there is a big difference. A cross join, just mentioning a comma separated list of tables in the from clause of a query, most seldom makes any sense, while a FULL join can show you orphaned data, for example.

Do you know any more databases besides MySQL, Chris?

Bye, Olaf.

 

[Olaf Doschke]

Disregarding the topic of sql join types. If you want to show nested data, like for example customers and their currently open orders, you can do this in different ways. One would be to make one query containing both customers and their orders, that would typically be a left join from customers and orders, or you first only query customers and then for each customer do a query of only his open orders.

In the first case you'd only have one while() loop on the php side, which would in general have multiple iterations per customer. It's less easy to process for that, but you only need to query the database once.

In the second case we could name this nested query, as you do a query per customer within the while loop for all customers. Then your inner query would have something like WHERE customerID = $row1['customerid'], so you only fetch the rows of the order table, which are of that customer.

You have to have a customerid in your order table, for that matter.

I don't know the structure of your typeofcall and report2 tables. They both seem to be on the topic of phone calls, but without any conjunction between the two tables no join makes any sense and I also don't see a sense of nesting the two while loops, then. Your result html seems to have one section for typeofcalls, and one for report2. These are not nested in each other like customers and their orders are nested.

Bye, Olaf.

 

[ChrisHirst]

You seem to be thinking that my 'off the cuff' remarks about joining two tables in one query was a complete treatise or discourse on SQL (of any 'flavour') joins.

IT IS NOT get over it.


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]

OK, so this is how you react, if you realize you're wrong? Sure it is a side topic, but you could simply say thank you in the gist of your own saying:

'Not knowing' is a good thing, because it means there is more to learn.

This is only valid for others, not for yourself? Or what?

Bye, Olaf.

 

[ChrisHirst]

Read the post you are complaining about.

Effectively there are, it's a 'FULL JOIN' performed using PHP,

NO CODE, I use the words FULL JOIN as a example, an ANALOG of what the PHP is doing. That is joining all matching records from BOTH tables. Take careful note of the word "effectively meaning [in this context] "... ... is behaving as if there is were a full join applied.

You appear to be berating me for providing erroneous code, when I have provided NO CODE AT ALL and HAVE NOT intended ANY of my comments to be taken as specific code for a query to be run in any particular version of Structured Query Language.



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.