[RESOLVED] Nested Query

mAGOO

Hi all and thanks for reading my problem.

Background:
I'm trying to output results from 2 queries into one page and I simply can't get my head round it. I'm hoping that someone can enlighten me or at least point me in the right direction.

OK now for the queries.

I have two:

First one gets me the members I want along with their ID's that I can use for the second query.

SELECT pilots.id, reports.id, MAX(reports.datefiled) AS lastfiled, pilots.id, pilots.name, pilots.country, pilots.hub, pilots.status, pilots.fpi, pilots.ivao, pilots.vatsim, reports.id, reports.duration, reports.result, pilots.rank, pilots.admission_date
FROM pilots, reports
WHERE pilots.id = reports.id AND result='Completed' AND purpose NOT LIKE 'GA' AND reports.datefiled > '2008-07-01 00:00:00'
GROUP BY reports.id
ORDER BY reports.id asc

This one does an extra bit of math required. It basically add all their hours that have been accepted along with total distance and fuel.

SELECT COUNT(reports.id) AS T_PIREPS, SEC_TO_TIME( SUM( TIME_TO_SEC(reports.duration))) AS T_TIME, SUM(reports.distance) AS T_DIST, SUM(reports.fuel) AS T_FUEL
FROM pilots, reports
WHERE pilots.id = reports.id AND result='Completed' AND purpose NOT LIKE 'GA' 
GROUP BY reports.id
ORDER BY reports.id asc

The problem:
What I'm trying to do is to have the second query nested in the first so all members who fall within the date criteria (depending on 'datefiled' field) will be returned and also have their details returned in full for their entire history, not just the period from 'datefiled'.

The first query gathers all the standard info and the second does the math.

From what I've been reading up on, subqueries seems to be the way to do it, however, for the life of me I just can't seem to get the syntax right. Would anyone be able to give me a hand?

Cheers
Steph.

Kudose

Subqueries are really easy. All you need to remember is to wrap your subquery in parenthesis and make sure your query only returns one column and one result.

This is obvisouly untested

SELECT pilots.*, reports.*, 
(SELECT COUNT(id) FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_PIREPS, 
(SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(duration)) FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_TIME, 
(SELECT SUM(distance) FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_DIST, 
(SELECT SUM(fuel) FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_FUEL, 
FROM pilots LEFT JOIN reports ON pilots.id = reports.id 
WHERE reports.result = 'Completed' 
AND reports.purpose != 'GA' 
GROUP BY reports.id
ORDER BY reports.id ASC

A couple things to consider would be to change your Completed value to a digit to reduce space and search times. Also, in your query "NOT LIKE" starts a regex type search and is more intensive than a comparison.

Also, I got lazy and used * in pace of your columns, you will probably want to add them back in for performance.

mAGOO

LOL they maybe easy for you......

Thanks for the reply, but I'm still having problems.

I get 1064 error so I checked my MySQL version on the site and it's "4.1.22-standard". I also checked my php version and it's "5.2.6". Not sure why host hasn't upgraded MySQL to a later version as they have with php, but thats a diff story.

I then looked up the 4.1.22-standard manual for the syntax error and played with several variations. All I managed to do was to move the error from line 2 to all the other lines.

Can I also ask a question about the code you put forward, what is the significance of the '!'? why do you have it in some and not in others?

I do apologise for these simple questions but I taught myself from using and modifying others' code and doing what I needed to do rather than learning from the usual way of reading start to finish. This I'm quickly coming to regret I must say as I've not been doing much code this yr and I've basically lost a lot of my knowledge.

Anyway, hope you can help a drowning sailor in a sea of CONFUSION.

Cheers
Steph.

Kudose

The ! in front of = means not equal to instead of equal to.

What is the SQL error your getting? It should be like, "You have a syntax error near ??? on line ???. Check the manual cause you messed up"

mAGOO

Sorry I should have posted that.

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 'FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_TIME,
(SELEC' at line 3

I tried adding the table names but this gave me the same error too. eg "reports.purpose != 'GA' " etc.

Cheers
Steph.

Kudose

Remove the comma after T_FUEL before FROM

mAGOO

Nope, still same.

SELECT pilots.*, reports.*,
(SELECT COUNT(reports.id) FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS T_PIREPS,
(SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(reports.duration)) FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS T_TIME,
(SELECT SUM(reports.distance) FROM reports WHERE result = 'Completed' AND purpose != 'GA') AS T_DIST,
(SELECT SUM(reports.fuel) FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS T_FUEL
FROM pilots LEFT JOIN reports ON pilots.id = reports.id
WHERE reports.result = 'Completed' AND reports.purpose != 'GA'
GROUP BY reports.id
ORDER BY reports.id ASC

Error Msg:

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 'FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS ' at line 3

Kudose

Ah, there is a missing parenthesis.

SEC_TO_TIME(SUM(TIME_TO_SEC(reports.duration))

Should be

SEC_TO_TIME(SUM(TIME_TO_SEC(reports.duration)))

mAGOO

Bingo!

Could you pls tell me how you got to that?

mAGOO

one sec.....

mAGOO

So close....

http://www.ansett-va.com/an_roster/index_new.php

mAGOO

no error but no winner either.

Kudose

I saw that at the end of the error there was the word AS, so I looked at the sub-queires for an error.

Try altering your query and running it in phpMyAdmin or MySQL until you get valid results.

What do you mean by "no winner either". Do you mean you don't get any results or you don't get the results you expect?

mAGOO

The result should be closer to :

http://www.ansett-va.com/an_roster/

this has fisrt query used and it's all working fine as u can see.

Second query is used in their details when u click on their ID.

The aim is to get these members only, and also only show their totals. I'm trying to have a time limit so only avtive members are shown and their total times shown.

So if member X last filed a report 12months ago, they would not show in this list, however, if they filed a report 1 week ago, they woud be automatically listed and their total times would also be displayed.

Kudose

What does a returned record look like?

i.e. print_r a row and post it back please.

mAGOO

OK it is almost there. The only thing that it is not doing correctly is adding the hrs and reports by member. It is counting them as a whole.

You will also see that I have made a couple of changes. Added MAX to display the most recent date a report was filed. I have also added a cutoff date which causes only the members that have filed a report in the past month to be displayed based on their lastdate.

I have also removed T_FUEL and T_DIST just to simplify the query at this stage. Until I can get my head round this, that is.

SELECT pilots.id, reports.id, MAX(reports.datefiled) AS lastfiled, pilots.id, pilots.name, pilots.country, pilots.hub, pilots.status, pilots.fpi, pilots.ivao, pilots.vatsim, reports.purpose, reports.id, reports.duration, reports.result, pilots.rank, pilots.admission_date,
(SELECT COUNT(reports.id) FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS T_PIREPS,
(SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(reports.duration))) FROM reports WHERE reports.result = 'Completed' AND reports.purpose != 'GA') AS T_TIME
FROM pilots LEFT JOIN reports ON pilots.id = reports.id
WHERE reports.result = 'Completed' AND reports.purpose != 'GA' AND reports.datefiled > '2008-07-01 00:00:00'
GROUP BY pilots.id
ORDER BY reports.id ASC, reports.datefiled DESC

The results I get.

CORRECT 9
CORRECT CAT1
CORRECT cat1
CORRECT Joe Howell
CORRECT Perth
INCORRECT 106302:21:52 (this is the total hrs across all members)
CORRECT Inactive
CORRECT 2003-02-01
CORRECT 2008-07-26 20:50:40
INCORRECT 7891 (this is the total reports across all members)

Kudose

Ok, you need to get the top level pilots ID into the sub query ... I am sorry, but I haven't had to do this before.

I think you want:

SELECT pilots.id, reports.id, MAX(reports.datefiled) AS lastfiled, pilots.id, pilots.name, pilots.country, pilots.hub, pilots.status, pilots.fpi, pilots.ivao, pilots.vatsim, reports.purpose, reports.id, reports.duration, reports.result, pilots.rank, pilots.admission_date,
(SELECT COUNT(reports.id) FROM reports WHERE reports.id = pilots.id AND reports.result = 'Completed' AND reports.purpose != 'GA') AS T_PIREPS,
(SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(reports.duration))) FROM reports WHERE reports.id = pilots.id AND reports.result = 'Completed' AND reports.purpose != 'GA') AS T_TIME
FROM pilots LEFT JOIN reports ON reports.id = pilots.id
WHERE reports.result = 'Completed' AND reports.purpose != 'GA' AND reports.datefiled > '2008-07-01 00:00:00'
GROUP BY pilots.id
ORDER BY reports.id ASC, reports.datefiled DESC

mAGOO

That is perfect.

A big thank you !

Thanks for your patience too. I just had major probs trying to work this out for some reason. I suppose it comes from not doing them on a regular basis.

Tell me, is this an efficient way for such a query? or is there another method I should be looking at also.

Kudose

I am not really a DBA, but I believe it should be fairly efficient. Like I said before, you can change Complete to a numeric value to save in processing time. Databases are often tuned by default to handle up to four joins without much lag and I haven't really read anything on sub queries performance wise. Using the one query above is far more efficient than using one query to get the general results and doing another query based on the first queries results.