INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Nested Query

Nested Query

(OP)
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

CODE --> php

//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?

RE: Nested Query

Quote:

Please, any Ideas?

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

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

Scroll down to:

Quote (W3 Schools)

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.

RE: Nested Query

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: PHP


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

RE: Nested Query

Quote:

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.

RE: Nested Query

CODE

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.

RE: Nested Query

(OP)
Thanks for your answers!!!!

RE: Nested Query

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

RE: Nested Query

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.

RE: Nested Query

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.

CODE

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.

RE: Nested Query

(OP)
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

RE: Nested Query

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

CODE --> php

$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>"; 

RE: Nested Query

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.

RE: Nested Query

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

RE: Nested Query

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.

RE: Nested Query

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.

RE: Nested Query

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):

CODE

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.

RE: Nested Query

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.

RE: Nested Query

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.

RE: Nested Query

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:

Quote (Chris)

'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.

RE: Nested Query

Read the post you are complaining about.

Quote:

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.

RE: Nested Query

You said:

Quote (Chris)

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.

RE: Nested Query

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

CODE --> SQL

FULL JOIN 
cannot be performed instead of a

CODE --> MySQL

CROSS JOIN 
, and a

CODE --> MySQL

CROSS JOIN 
cannot be performed instead of a

CODE --> SQL

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.

RE: Nested Query

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.

RE: Nested Query

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.

RE: Nested Query

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.

RE: Nested Query

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close